Calculate the median of diferent sum of products of customers

[dobregon]

Hi,

I'm trying to calculate the median from a sum of values and i have some problems. Exmaple to understad my problem.

1. I have this table of values
   

   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

2. I want to sum the column [Value] for each customer and then with the sums of A, B, C then calculate the median of the 3 values. like the next example
   

   Customer	Sum and then median
   A	231
   B	103
   C	50
   Total (median of the 3 values)	103

   
   
   
3.  Then I want to have the 103 in each row as:
   

   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!

[EugenioJunior]

@dobregon 

see this solution

SUM = 
MEDIANX(
    VALUES('Table1'[Customer]),
    CALCULATE(
                SUM('Table1'[Value])
                          )
                  )

MEDIANX = 
MEDIANX(
    ALL('Table1'[Customer]),
    CALCULATE(
                SUM('Table1'[Value])
                           )
                   )

[dobregon]

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!!

[EugenioJunior]

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.

[Zubair_Muhammad · Accepted solution]

@dobregon @EugenioJunior 

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]

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!

[Zubair_Muhammad]

@dobregon 

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] ) )
)