Multiply by line

[Ben Niebuhr]

I have been working hard to implement PowerPivot in our processes, because I love the power it gives. I am quickly getting my internal costomers addicted to the quick results, but they are now asking for things that are taxing my skill. WHICH IS FANTASTIC!!

I usually work with hourly data, so the amount can pile up fast. I prefer to keep my tables tall and skinny (thanks for the post on this, Rob Collie). I have a table that looks like this:

DateTime Customer DataType DataValue
1 a Reading 438.22
1 a Rate 0.7622
1 b Reading 618.69
1 b Rate 0.1101
2 a Reading 526.41
2 a Rate 0.7869
2 b Reading 468.41
2 b Rate 0.5245
3 a Reading 701.62
3 a Rate 0.184
3 b Reading 931.5
3 b Rate 0.6168
(sorry, I can't figure out how to make the table all nice.)

I need to write a measure that will multiply each reading to its corresponding rate. I have solved this by writing SQL to create a 'short, fat' table making each data type a column, but this really limits my ability to pull the quantity of data I need.

Any help would be greatly appreciated.

Ben

ps. the datetime column is related to a date table that I use for all my filtering/slicing needs. (Another great tip from the PowerPivotPro)

 

[David Churchward]

Hi Ben

To be honest, I suspect that holding two separate columns for your values is probably going to be the best approach. You can multiple the two together at runtime.

Having said that, there is an answer with your current structure, but I'm not sure how it's going to perform with huge datasets.

I've called the table you supplied Table1. I've added a column called ID which matches the DateTime and Customer combinations. I've listed these IDs in a separate Table2 and created a relationship on ID.

Table1
<TABLE style="WIDTH: 278pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=370><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 60pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=80>Datetime</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 62pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=82>Customer</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 60pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=80>DataType</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Value</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>438.22</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.7622</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>618.69</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.1101</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>526.41</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.7869</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>468.41</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.5245</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>3</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>701.62</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>3</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">a</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.184</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20>3</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none">Reading</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>931.5</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" align=right>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>3</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">b</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">Rate</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>0.6168</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>60</TD></TR></TBODY></TABLE>

Table2
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=64>ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20 align=right>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20 align=right>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dce6f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DCE6F1 none" height=20 align=right>50</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20 align=right>60</TD></TR></TBODY></TABLE>

This formula should now work

=
SUMX
(
Table2,
CALCULATE(SUM(Table1[Value]),
Table1[DataType]="Reading")
*
CALCULATE(MAX(Table1[Value]),
Table1[DataType]="Rate")
)

I hope this helps but, like I said, I'm not sure how well it will perform with huge datasets with a high number of IDs / Unique combinations. I'm sure The Italians and Rob will come up with something much more flash.

Good luck
David

 

[Ben Niebuhr]

Thank you, David. I appreciate your time and effort, but the example I gave was a simplified version of the issue. I actually have six data types, roughly 100 customers, and about 9,000 time periods. So I would need about 5.5M unique IDs.

I am currently playing with making six different data pulls, one for each data type, but since they need to be combined on date and customer, I don't know how to get them connected. A concatenated key maybe? My problem is that I can come up with ways to make it work with a sample of the data, but when I try to process the "mountain range" everything crawls. My IT group is helping me with a special machine to due this type of processing, but I think my problem is inefficiency through ignorance.

By the way, I have really enjoyed and learned from your posts. You have some great techniques for applying the power to accounting reports.

 

[David Churchward]

Hi Ben

Have you tried holding multiple value columns (ie value column for reading, value column for rate etc)? Because of the structure of your dataset, I'm not sure that there's too much to be gained by keeping the number of columns to a minimum (although equally it isn't advisable to have too many columns unnecessarily). You've still got a high number of distinct values in that column and by keeping all your values in that one column, I believe that I'm right in saying that only one core will be used. By holding separate columns, you'll have fewer distinct values per column and you'll be able to scale across cores.

What spec machine are you using? 64 Bit, quad core, 8GB RAM will conquer most things.

With larger datasets where you can't use a related table with "keys /IDs", you'll get the most performance gain from keeping your measures simple.

Hope this helps. Maybe the "Pros" will pitch in with some vamped up masterpieces.

Thanks for your kind words and good luck.
David

 

[David Churchward]

Hi Ben

Just checked your first message and you have indeed tried the "short / fat" approach. What was limiting you from pulling the quantity of data that you needed?

Apologies for not picking that up sooner.
Thanks
David

 

[Ben Niebuhr]

My main issue with short / fat approach is system performance. I am still pretty new to SQL as well, so I am working on making that more efficient as well. I am working with 32bit now, but am testing a network resource that is 64bit. I think this would be a good test of that machine.

In theory, the new computer should have all the power I could want. Since I work with hourly data I can get buried pretty quickly, so I want to learn to do things as efficiently as possible from the start. I just upgraded from excel 2003 early last year, and now that I have access to hundreds of millions of rows, I don't know how I used to do my job.

 

[Ben Niebuhr]

Incase anyone is interested, I solved my issue in two ways with a great deal of inspiration from the suggestions that David made, as well as this post: www.powerpivotpro.com

I agree with David's previous suggestion, that this would be best as a "Short / Fat" table, and once I learned that the table I was querying was actually a view, the performance was vastly improved. I think this is actually the solution I will be using for my report.

But I solved this with measures first. And the performance wasn't too bad, even on my poor, overworked, 32bit laptop (running XP, no less).

Here is how I did it:

1. Create a "kind-of" Key Column
   I contantinated the DateTime and Customer columns into a calculated column on table1. Similar to what David suggested in his examples above. This gave me something to reference that was unique to each time period for each customer. (I actually did this in SQL, since I had roughly a million rows in my data table.)
   
2. Write base Measures
   I created a measure to refer to each DataType
   ex: [Reading] = calculate(sum(table1[Value]),table1[DataType]="Reading")
   
3. Write Final Calculation Measures
   I then wrote measures using the tips from the PowerPivotPro link above to do the calculations I needed, referring to the key column to keep everything at the level I needed.
   ex: [Total] = SUMX(values(table1[Key]),[Reading]*[Rate])

The subtotals and grand totals work beautifully, and it gave me lots of education that will be invaluable in other projects. I was convinced the EARLIER() DAX function would help me; so far it is to me what calculus is to a dog.

I hope this helps someone else out there.

Ben

 

[Ben Niebuhr]

I see now, that what I did with the measures was exactly what David had suggested, but without the second table, and with the Calculate() items seperated out.
I guess I just wasn't ready to understand his response. Thanks again.

 

[David Churchward]

Glad you sorted it out Ben and thanks for the update.