cancel
Showing results for
Did you mean:
Highlighted
Member

Calculate the median of diferent sum of products of customers

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Calculate the median of diferent sum of products of customers

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

6 REPLIES 6
Super User

Re: Calculate the median of diferent sum of products of customers

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] ) )
)
```
Frequent Visitor

Re: Calculate the median of diferent sum of products of customers

@dobregon

see this solution

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

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

Member

Re: Calculate the median of diferent sum of products of customers

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?

Frequent Visitor

Re: Calculate the median of diferent sum of products of customers

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.

Super User

Re: Calculate the median of diferent sum of products of customers

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

Member

Re: Calculate the median of diferent sum of products of customers

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!