Forum Discussion
Measure in a Measure
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
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
THANK YOU THANK YOU THANK YOU! That works like a charm. Beautiful. I never would've come up with that. I will have to learn more about this summarize funcitonality! Beautiful. Thanks again!!!