- 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

Turn on suggestions

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
- Re: Median of distinct total per month

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

Mr_Glister

Regular Visitor

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

02-24-2017
01:01 AM

Hi,

I can't believe how much difficulty this apparently easy problem is causing me.

Below is a sample set of data. Imagine it to be a list of all sales to the customers of our company.

We see that for instance in Month 1 we had 1 sale to customer AAA, 3 sales to BBB, 1 to CCC, and 2 sales to customer DDD.

I added the calculated column Sum per month = CALCULATE(sum(Sheet1[Volume]);ALLEXCEPT(Sheet1;Sheet1[Customer Name];Sheet1[Month]))

For Month 1 it correctly gives me:

AAA = 380

BBB = 895

CCC = 100

DDD = 388

However, the calculated column of course repeats these values in each row where the same customer shows up within the same month again.

Now all I want to do is create a measure (unless you have better ideas) that correctly calculates the Median of the customers' totals each month. So for Month 1 the correct value would be 384, for Month 2 it's 829 and for Month 3 it's 404.

I think the solution is to tell the Median to only consider the distinct values within a month. Of course I tried doing that but the results I got where never correct, even when I created a calculated table with only the distinct values. Really hope one of you can show me how easy it is and I can sleep again at night!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

Excelside

Established Member

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

02-24-2017
04:44 AM

Well ok sorry I hadn't taken into account the fact that the customer can appear several times in the same month.

In that case:

Med = MedianX ( Values(YourTable[Customer Name]) , [Total amount] )

will work because the grain of the iteration has been adjusted to what you want.

5 REPLIES 5

Excelside

Established Member

Re: Median of distinct total per month

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

02-24-2017
03:08 AM

Hi @Mr_Glister

You can remove your calc column and use MedianX function.

Create a simple measure: Total Amount = Sum(YourTable[Volume] )

And your median measure is:

Med = MedianX ( YourTable , [Total amount] )

This measure uses **context transition.**

Mr_Glister

Regular Visitor

Re: Median of distinct total per month

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

02-24-2017
04:27 AM

hi @Excelside

Thanks for the quick reply but this doesn't seem to be the correct solution.

I did what you say but this produces only the Median of the individual volumes per month.

I get in fact the same result if I only do =MEDIAN(MyTable[Volume]).

But that is not what I need. It is each customer's total per month of which I want to calculate the Median from.

Your solution produces this for me:

Highlighted

Excelside

Established Member

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

02-24-2017
04:44 AM

Well ok sorry I hadn't taken into account the fact that the customer can appear several times in the same month.

In that case:

Med = MedianX ( Values(YourTable[Customer Name]) , [Total amount] )

will work because the grain of the iteration has been adjusted to what you want.

Mr_Glister

Regular Visitor

Re: Median of distinct total per month

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

02-24-2017
05:18 AM

It was as easy as that! :-)

Thank you very much!

Excelside

Established Member

Re: Median of distinct total per month

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

02-24-2017
05:24 AM

The logic running behind is not as easy as the simplicity of the syntax

The mix of X functions with context transition + Values() or All () to alter the grain of your Table argument is a classic pattern that helps solve many DAX issues !