Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Hoping someone can help as I can't find any information on this...
I want to calculate the median price for some pre-aggregated data - I don't have access to the line-level data.
A simplified table looks like this:
Total Spend | Quantity | Price |
25 | 5 | 5 |
20 | 2 | 10 |
40 | 10 | 4 |
This should give a median of 4. But how do I write that calculation?
Thanks in advance
Solved! Go to Solution.
@s45kougo - Here is a much more elegant solution, Page 32, PBIX Table (32) attached below sig.
Measure 32 =
VAR __Table =
GENERATE(
'Table (32)',
VAR __Quantity = [Quantity]
RETURN GENERATESERIES(1,__Quantity,1)
)
RETURN
MEDIANX(__Table,[Price])
@s45kougo - Here is a much more elegant solution, Page 32, PBIX Table (32) attached below sig.
Measure 32 =
VAR __Table =
GENERATE(
'Table (32)',
VAR __Quantity = [Quantity]
RETURN GENERATESERIES(1,__Quantity,1)
)
RETURN
MEDIANX(__Table,[Price])
Oh wow, you are amazing , thank you *so* much! This works perfectly. I thought it must be possible, but I had no idea how to disaggregate a table. Hope this helps someone else too in the future 🙂
@s45kougo - There may be a more elegant solution and I will think about it but for this you could brute force it:
Measure =
VAR __Table25 =
ADDCOLUMNS(
GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=25),[Quantity]),1),
"Price",MAXX(FILTER('Table',[Total Spend]=25),[Price])
)
VAR __Table20 =
ADDCOLUMNS(
GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=20),[Quantity]),1),
"Price",MAXX(FILTER('Table',[Total Spend]=20),[Price])
)
VAR __Table40 =
ADDCOLUMNS(
GENERATESERIES(1,MAXX(FILTER('Table',[Total Spend]=40),[Quantity]),1),
"Price",MAXX(FILTER('Table',[Total Spend]=40),[Price])
)
VAR __Table = UNION(__Table25,__Table20,__Table40)
RETURN
MEDIANX(__Table,[Price])
@s45kougo , In case this data is already aggregated. You can average
Avg = Divide(Sum(Table[Total Spend]),sum(Table[Quantity]))
Which will be five
You can try MEDIAN( Table[Price] )
But I doubt you can get 4
@amitchandak thanks for trying, but I do want the actual median of 4... I'm guessing that somehow I have to calculate a table that dis-aggregates, but not sure how to do this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
146 | |
106 | |
104 | |
89 | |
65 |