Calculating decile over several categories and dates

Anything related to PowerPivot and DAX Formuale
dforne
Posts: 1
Joined: Fri Aug 14, 2020 11:36 am

Calculating decile over several categories and dates

Postby dforne » Fri Aug 14, 2020 11:38 am

Hi,
 
I am trying to calculate to which decile belongs the clients of the data set below in powerpivot.
The complexity is that the data set is an aggregation of several reports (hence the report date), and that the items sold are grouped into 2 categories, one of those being a subcategory of the other one.
End of the day, I want to be able to tell for each client ID either for a specific report or across all of them, either for a specific category or across all of them, in which decile he belongs.

Additional request, as 80% of my clients have only 1 items sold, it would be nice to put all of the clients with only 1 items sold in each report into the last "decile" and spread the rest of the clients over the remaining 9 deciles.
 
I have tried different combination of measures and column with Rankx, Maxx and Percentile without any luck so far, any help would be much appreciated
 
Client ID Report Date Client Category Items Sold
1 dd/mm/2020 A 1
1 01/08/2020 A+B 1
1 01/07/2020 A+B 1
2 01/06/2020 A+B 3

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron