Good Afternoon
I was hoping that someone could help me with my DAX woes.
According to the docs, GROUPBY with CURRENTGROUP doesn't support the MEDIANX or PERCENTILEX aggregations. I see that SUMMARIZE can be used as an alternative but SUMMARISE doesn't seem to be able to handle aggregations on virtual tables (which means I can't filter for unique values).
I have data that is essentially of the form found in the table below:
| region | broker | metric | misc 1 | misc 2 |
| a | aa | 60 | zQqYs\ | lorvA[ |
| a | aa | 60 | ECOSCj | NQLJLH |
| a | ab | 45 | J[Xi[a | MXGfyy |
| a | ab | 45 | `LopyS | IWbv_d |
| a | ab | 45 | qOYCjE | OwCd_V |
| b | bc | 120 | ZYnNb_ | vjJKXn |
| b | bd | 75 | M^rthX | CU`XtX |
| b | bd | 75 | n]H]qK | qZG`dU |
| b | be | 30 | QlHAKz | iAHKMF |
The value of metric is unique for each broker. I would like to get the average of the median broker per region (so as to give equal weight to each region in calculating the "median")
I've tried to calculate this by retrieving the unique region/broker/metric combinations as below:
| region | broker | metric |
| a | aa | 60 |
| a | ab | 45 |
| b | bc | 120 |
| b | bd | 75 |
| b | be | 30 |
Then aggregating to get the median per region:
| region | median(metric) |
| a | 52.5 |
| b | 75 |
And finally taking the average to get the final "median"
| Average(median(metric)) |
| 63.75 |
My measure currently looks like this:
median_region =
VAR __tbl =
SUMMARIZE (
GROUPBY ( table, table[region], table[broker], table[metric] ),
[region],
"median", MEDIAN ( [metric] )
)
RETURN
AVERAGEX ( __tbl, [median] )
OR alternatively
median_region =
VAR __tbl = SUMMARIZE (
DISTINCT (
SELECTCOLUMNS ( table, "r", [region], "b", [broker], "m", [metric] )
),
[b],
"median", MEDIAN ( [m] )
)
RETURN
AVERAGEX ( __tbl, [median] )
Either way SUMMARISE cannot identify the context of the column [metric] so it results in an error.
