Forum Discussion

Icon for Helper I rank

Helper I

4 years ago

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

Icon for Super User rank

Super User

4 years ago

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

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

Icon for Helper I rank

Helper I

4 years ago

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

VahidDM
Super User
4 years ago
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/
- 2366
  Helper I
  4 years ago
  I have shared it in DM. I have applied the date table now
  - VahidDM
    Super User
    4 years ago
    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:
    
    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/

Related content