Forum Discussion
Issue with medianx
Hi all ,
I have the following problem . I am have 3 columns . Two has values and one has locations .
I divide value1/value2 and i need to calculate the median per location and the total median .
1) If i create a calculated column C1 and then create a mesaure C2=median(C1) i get as result V1
2) if i create the following measures S1=sum(value1) , S2=sum(value2) , S3=divide(S1,S2) and then
S4=MEDIANX(KEEPFILTERS(VALUES('Sheet1'[Locations])),(S3)) i get as result V2
3) if i skip creating measures S1,S2,S3 and do all the calculations inside one measure as the above
| Query |
| SELECT 'Sheet1'[Locations], SUM ( 'Sheet1'[V1] ), SUM ( 'Sheet1'[V2] ) FROM 'Sheet1'; |
| Query |
| SELECT SUM ( 'Sheet1'[V1 ), SUM ( 'Sheet1'[V2] ) FROM 'Sheet1'; |
2 Replies
- Greg_Deckler
Community Champion
Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. HI Anonymous,
It seems like you want to apply multiple aggregates to your formulas.
For this scenario, I'd like to suggest creating a variable with summarize function to apply the first aggregate, then use 'iterator' medianx function to apply the second aggregate:S4 = VAR summary = SUMMARIZE ( 'Sheet1', [Locations], "_S3", DIVIDE ( SUM ( 'Sheet1'[Value1] ), SUM ( 'Sheet1'[Value2] ) ) ) RETURN MEDIANX ( summary, [_S3] )Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
