Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
2366
Helper I
Helper I

DAX - Percentile Calculation at different granularity

Hello,

 

I have a data set which is at multiple granularity - Region, Country, City, Daily with Profit as my measure

Now I want to calculate percentiles at different grain dynamically.

 

2366_0-1639479594349.png2366_1-1639479680680.png

 

 

My Dax is as follows -

CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[City]),[Margin],.5))

CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[Country]),[Margin],.5))

 

For example - 1) top 50 percentile for each city for each month

2) top 50 percentile for each country in a region for each month 

 

I have been able to get this to work for each country and city for all months but the percentiles need to be applied on a monthly basis. 

Can you help please?

 

Thank you

13 REPLIES 13
v-xiaotang
Community Support
Community Support

Hi @2366 

Do you mean you want to get Percentile_City and Percentile_Country for each month?

vxiaotang_0-1639716479251.png

If yes, try to filter the table like bellow,

CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[City]),[Margin],.5),filter(All(Sheet1),Sheet1[Month]=min(Sheet1[Month]) && Sheet1[City]=min(Sheet1[City])))
CALCULATE(PERCENTILEX.EXC(VALUES(Sheet1[Country]),[Margin],.5),filter(All(Sheet1),Sheet1[Month]=min(Sheet1[Month]) && Sheet1[Country]=min(Sheet1[Country])))

If this doesn't work, could you provide a sample file? Thanks

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

VahidDM
Super User
Super User

Hi @2366 

 

Try this:

CALCULATE(PERCENTILEX.EXC(ALLEXCEPT(Sheet1,Sheet1[City],Sheet1[Month]),[Margin],.5))

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Hi @VahidDM ,

Thanks but it returns blank as it doesnt seem to calculate percentiles at Monthly/City grain

 

2366_0-1639481830756.png

 

@2366 
Try this:

for City:
PERCENTILEX.EXC(Sheet1,[Margin],.5))
For Country :
CALCULATE(PERCENTILEX.EXC(Sheet1,[Margin],.5),removefilters(Sheet1[City]))

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 



@VahidDM that worked partially but it appears the filter for month is also being removed.

I need the percentiles to be reset for every month/year

 

2366_0-1639482386565.png

 

Do you have any Date table? what is the source of that Month Column?


Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

No, I do not have an explicit Date table. I am using the inbuilt date hirearchy "Month" column in the visual

So It would be better to add a Date table to your model then it will be easier to find a solution.
How to create a Date table:https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

 

Can you share a sample of your PBIX file after removing sensetive data?

 


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

 

I have shared it in DM. I have applied the date table now

Try these two:

Quartile 2_City =
VAR _A =
    SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[City], "M", [Margin] )
RETURN
    PERCENTILEX.EXC( _A, [M], .5 )

 

Quartile_Country =
VAR _A =
    SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[Country], "M", [Margin] )
RETURN
    PERCENTILEX.EXC( _A, [M], .5 )

 

Output:

VahidDM_0-1639485538059.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Both of them are returning same values 😞

2366_0-1639486466186.png

 

Change Country to this:

Quartile_Country = 
VAR _A =
    calculatetable(SUMMARIZE( Sheet1, Sheet1[Date], Sheet1[Country], "M",[Margin]),removefilters(Sheet1[City]))
RETURN
    PERCENTILEX.EXC( _A, [M], .5 )

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Sorry, that doesn't seem to change either

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors