I'm trying to calculate the median from a sum of values and i have some problems. Exmaple to understad my problem.
|Customer||Sum and then median|
|Total (median of the 3 values)||103|
|Customer||Sum and then median||Total Median of sum|
I'm trying with summarize, medianX but I didn't get the median after the individual sum. Can someone help me on this?
Solved! Go to Solution.
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] ) ) )
see this solution
SUM = MEDIANX( VALUES('Table1'[Customer]), CALCULATE( SUM('Table1'[Value]) ) )
MEDIANX = MEDIANX( ALL('Table1'[Customer]), CALCULATE( SUM('Table1'[Value]) ) )
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|
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|
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)|
I have tried in powerbi and i always receive the sum of values instead of medians when i use the both formulas
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!!
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.