Forum Discussion
Measure in a Measure
OK I know this is stupid I'm just hitting a wall and my brain is fried. I must be searching for the wrong thing. I have two tables such as:
Event Table
| Event | Metric Component | Date/Time | Year-Mon |
| 1 | 10 | 1/1/2018 5:30AM | 2018-1 |
| 2 | 42 | 1/3/2018 5:30AM | 2018-1 |
| 3 | 5431 | 2/1/2018 5:30AM | 2018-2 |
| 4 | 249 | 2/1/2018 6:30AM | 2018-2 |
Customer Count table:
| Year-Mon | Customer Count |
| 2018-1 | 600 |
| 2018-2 | 650 |
Which currently I use excel to develop something along the lines of this to analyze the 'Metric Total' which is measured as Metric Component / Customer Count:
| Year-Mon | Metric Component | Customer Count | Metric Total |
| 2018-1 | 52 | 600 | 0.09 |
| 2018-2 | 5680 | 650 | 8.74 |
| Total: | 5732 | 625 | 9.17 |
In PowerBI I have my two tables, the Year-Mon column is a calculated column. Now the problem is that the customer count needs to be averaged when it is aggregated. So before drilling down, I want Power BI to show me the "Total" row as in the resultant table above. Then as I drill down it would split into the monthly view, etc. In practice this will actually be an annual view for the past several years, but the user can drill into the current month.
So for the customer table, I have a measure defined as:
Avg Customers = average('Customers'[Customers])
Then for the 'result', I created a measure within the event table:
METRIC = SUM(‘Data’[Metric Component]) / SUM('Customers'[Avg Customers])
This throws an error because it doesn't like a measure in a measure apparently. I've also tried:
METRIC = Sum('Data'[Metric Component]) / calculate( sum('Customers'[Avg Customers]),AllSelected())
But I'm just hitting a brick wall. What in the world am I doing wrong?
Anonymous
For the tricky part you can use this measure:
Measure = AVERAGEX ( SUMMARIZE ( Table1; Table1[Year]; Table1[Month]; "CustomSum"; SUM ( Table1[Customers] ) ); [CustomSum] )Now you need to combine the measure with the other simple measure to evaluate the context and show what you want.
Regards
Victor
4 Replies
Anonymous
Hi, try with:
MetricTotal = SUM ( Data[Metric Component] ) / [Avg Customers]
Regards
Victor
Unfortunately that doesn't work because it averages across the whole set of customers.
I realize I may have screwed up in defining the problem because I know my issue is with how I'm handling the customers. Tried to keep it simple for the forum and I think I bit myself in the foot. Here's a more accurate description of the customer table:
Territory Customers Year Month YearMon A 37,381.00 2017 12 201712 B 47,416.00 2017 12 201712 C 10,106.00 2017 12 201712 D 109,685.00 2017 12 201712 E 64,144.00 2017 12 201712 F 9,551.00 2017 12 201712 G 152,264.00 2017 12 201712 H 61,934.00 2017 12 201712 I 20,968.00 2017 12 201712 J 10,536.00 2017 12 201712 K 14,323.00 2017 12 201712 L 7,801.00 2017 12 201712 M 101,184.00 2017 12 201712 N 30,221.00 2017 12 201712 O 17,931.00 2017 12 201712 P 15,918.00 2017 12 201712 Q 83,562.00 2017 12 201712 R 49,529.00 2017 12 201712 S 2,844.00 2017 12 201712 T 21,332.00 2017 12 201712 A 37,452.00 2018 1 20181 B 47,483.00 2018 1 20181 C 10,108.00 2018 1 20181 D 109,898.00 2018 1 20181 E 64,600.00 2018 1 20181 F 9,548.00 2018 1 20181 G 152,249.00 2018 1 20181 H 61,999.00 2018 1 20181 I 20,983.00 2018 1 20181 J 10,530.00 2018 1 20181 K 14,327.00 2018 1 20181 L 7,804.00 2018 1 20181 M 105,975.00 2018 1 20181 N 30,184.00 2018 1 20181 O 18,001.00 2018 1 20181 P 15,908.00 2018 1 20181 Q 83,549.00 2018 1 20181 R 49,557.00 2018 1 20181 S 2,835.00 2018 1 20181 T 21,348.00 2018 1 20181 A 10,512.00 2018 3 20183 B 14,583.00 2018 3 20183 C 7,795.00 2018 3 20183 D 105,777.00 2018 3 20183 E 30,154.00 2018 3 20183 F 18,058.00 2018 3 20183 G 15,895.00 2018 3 20183 H 83,514.00 2018 3 20183 I 49,475.00 2018 3 20183 J 2,826.00 2018 3 20183 K 21,353.00 2018 3 20183 L 37,498.00 2018 3 20183 M 47,551.00 2018 3 20183 N 10,104.00 2018 3 20183 O 109,784.00 2018 3 20183 P 64,638.00 2018 3 20183 Q 9,586.00 2018 3 20183 R 152,341.00 2018 3 20183 S 62,076.00 2018 3 20183 T 21,019.00 2018 3 20183
I have a calculated column that also concatenates the territory on each table so that I have a YearMonTerritory column such as 20182T which is the 'key' to tell each row in the event table which row in the customer table goes with it. There's 1 record in the customer table and many in the event table.
So if I'm drilled in on a single territory in a single month in a single year, say territory T in March of 2018, I want the denominate of my Metric function to be 21,019. But if I am looking at territory T in 2018, I want it to be the average of Territory T for 2018 which with the table above would be 21,183.5 . Then if I am looking at all years it would be 21,233 .
The tricky part is if I'm looking at a total or a subset of the territories, it needs to be the average of the sum of the territories over the time frame. So if I'm looking at all territories in March 2018, I want to use 874,539. If I'm looking at all of 2018 I want the average of the months showing in this table, or: 874,338 (for March) + 874,539 (For January) / 2 = 874,438.5 (for 2018). Then if I'm looking at all time, I would be using 872,502.3 .
So the definition you mentioned is the first syntax I tried, but it uses 43,726.9 for March 2018.
Anonymous
For the tricky part you can use this measure:
Measure = AVERAGEX ( SUMMARIZE ( Table1; Table1[Year]; Table1[Month]; "CustomSum"; SUM ( Table1[Customers] ) ); [CustomSum] )Now you need to combine the measure with the other simple measure to evaluate the context and show what you want.
Regards
Victor