- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculate the median of diferent sum of products o...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

dobregon

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
07:43 AM

Hi,

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

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Zubair_Muhammad

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
10:41 AM

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

Zubair_Muhammad

Super User

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
10:52 AM

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

EugenioJunior

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2019
12:22 PM

@dobregon

see this solution

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

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

dobregon

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
01:43 AM

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

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
07:20 AM

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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2019
10:41 AM

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

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-01-2019
12:14 AM

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!