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] ) ) )
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] ) ) )
@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!
The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
User | Count |
---|---|
325 | |
173 | |
74 | |
61 | |
49 |
User | Count |
---|---|
400 | |
288 | |
87 | |
80 | |
72 |