Measure in a Measure

[soldstatic]

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?

[Vvelarde]

@soldstatic

Hi, try with:

MetricTotal =
SUM ( Data[Metric Component] ) / [Avg Customers]

Regards

Victor

[soldstatic]

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.

[Vvelarde · Accepted solution]

@soldstatic

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

[soldstatic]

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!!!