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,
I'm trying to calculate the median from a sum of values and i have some problems. Exmaple to understad my problem.
Customer | Product | Value |
A | 1 | 27 |
A | 2 | 27 |
A | 3 | 50 |
A | 4 | 42 |
A | 5 | 28 |
A | 6 | 27 |
A | 7 | 30 |
B | 1 | 26 |
B | 2 | 11 |
B | 3 | 48 |
B | 4 | 18 |
C | 1 | 50 |
Customer | Sum and then median |
A | 231 |
B | 103 |
C | 50 |
Total (median of the 3 values) | 103 |
Customer | Sum and then median | Total Median of sum |
A | 231 | 103 |
B | 103 | 103 |
C | 50 | 103 |
total | 103 | 103 |
I'm trying with summarize, medianX but I didn't get the median after the individual sum. Can someone help me on this?
Kind regards!
Solved! Go to Solution.
This measure works with the sample data in a Table Visual
Median = MEDIANX ( CALCULATETABLE ( VALUES ( Table1[PRODUCT ID] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Customer] = SELECTEDVALUE ( Table1[Customer] ) ) ), CALCULATE ( SUM ( Table1[Value] ) ) )
@dobregon
see this solution
SUM = MEDIANX( VALUES('Table1'[Customer]), CALCULATE( SUM('Table1'[Value]) ) )
MEDIANX = MEDIANX( ALL('Table1'[Customer]), CALCULATE( SUM('Table1'[Value]) ) )
Thanks @EugenioJunior and @Zubair_Muhammad for the responses.
I have tried both solutions in the example and they work, but i have tried in my system and i can't use them. This is a better example that I'm trying to explain.
Customer | PRODUCT TYPE ID | PRODUCT ID | DATE | Value |
A | 1 | 1 | 1/1/2019 | 977 |
A | 1 | 1 | 1/2/2019 | 387 |
A | 2 | 2 | 1/1/2019 | 929 |
A | 2 | 2 | 1/2/2019 | 1139 |
A | 5 | 3 | 1/1/2019 | 402 |
A | 5 | 3 | 1/2/2019 | 159 |
A | 5 | 4 | 1/1/2019 | 344 |
A | 5 | 4 | 1/2/2019 | 586 |
B | 1 | 5 | 1/1/2019 | 1261 |
B | 1 | 5 | 1/2/2019 | 504 |
B | 2 | 6 | 1/1/2019 | 295 |
B | 2 | 6 | 1/2/2019 | 171 |
B | 5 | 7 | 1/1/2019 | 1086 |
B | 5 | 7 | 1/2/2019 | 846 |
B | 5 | 8 | 1/1/2019 | 1209 |
B | 5 | 8 | 1/2/2019 | 793 |
B | 5 | 9 | 1/1/2019 | 102 |
B | 5 | 9 | 1/2/2019 | 662 |
C | 1 | 10 | 1/1/2019 | 984 |
C | 1 | 10 | 1/2/2019 | 861 |
C | 2 | 11 | 1/1/2019 | 329 |
C | 2 | 12 | 1/2/2019 | 136 |
C | 5 | 13 | 1/1/2019 | 531 |
C | 5 | 13 | 1/2/2019 | 482 |
So as you can see, I have 3 Customers (A,B,C) and diferent type of products (imagen cars, bikes, boats) and for each type we can have 1 or more products inside the product type.
I have a page with slicers filters of dates and product type in order to analyze depends on the dates or product type that i have selected in the slicers.
As an example i have filter 1 and 2nd of Jan 2019 and the product type 5 in the slicers and i want to calculate something like that
Slicer dates filter the period, but the query Sum, median need to take only product type 5 | |||
CUSTOMER | PRODUCTID | SUM | Median |
A | 3 | 561 | 745.5 |
A | 4 | 930 | 745.5 |
B | 7 | 1932 | 1932 |
B | 8 | 2002 | 1932 |
B | 9 | 764 | 1932 |
C | 13 | 1013 | 1013 |
As you see, I want (for the slicers selected) the customer, different products for the product type selected in the slicer and then the sum of values for the dates selected and then other column with the median of the products for each customer.
For them calculate the dev something like
CUSTOMER | PRODUCT ID | SUM | Median | % (median/sum) |
A | 3 | 561 | 745.5 | 133% |
A | 4 | 930 | 745.5 | 80% |
B | 7 | 1932 | 1932 | 100% |
B | 8 | 2002 | 1932 | 97% |
B | 9 | 764 | 1932 | 253% |
C | 13 | 1013 | 1013 | 100% |
I have tried in powerbi and i always receive the sum of values instead of medians when i use the both formulas
the SUMfrom @EugenioJunior and @Zubair_Muhammad do the median in the total for the 3 customers not for each customer (i dont want a total value so, for me is not important)
And the median put the median for each customer but with the median of the total customers, but i want to have the median for each customer repetead in each productid for each customer.
Could you help me?
Thanks in advance!!
Hi, @dobregon
What visual are you using?
MEDIANX2 = MEDIANX(Values('Table2'[PRODUCT ID]), SUMX(Values('Table2'[PRODUCT ID]), CALCULATE( MEDIAN(Table2[Value]) ) ) )
Does that help?
If you do not help, tell me.
This measure works with the sample data in a Table Visual
Median = MEDIANX ( CALCULATETABLE ( VALUES ( Table1[PRODUCT ID] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Customer] = SELECTEDVALUE ( Table1[Customer] ) ) ), CALCULATE ( SUM ( Table1[Value] ) ) )
Thanks a lot @Zubair_Muhammad , this measure works good! and thanks @EugenioJunior for take your time to help me!
I didn't know function calculated table.
Kind regards!
You can use this MEASURE
Sum and then Median = IF ( HASONEFILTER ( Table1[Customer] ), SUM ( Table1[Value] ), MEDIANX ( VALUES ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Value] ) ) ) )
and this one
Total Median of Sum = MEDIANX ( ALLSELECTED ( Table1[Customer] ), CALCULATE ( SUM ( Table1[Value] ) ) )
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |