Forum Discussion
Biotech Deciling is Different - DAX HELP
Deciling Accounts by Sales in Biotech is a bit different than the typical n-tile type Deciling (breaking into 10 even groups), but is weighted based on the relative size of the Sales.
The process in Excel is first sorting the Accounts largest to smallest by Sales, then calculating the % of total, then calculating the running total of the % of total, then performing a vlookup (with TRUE) on the running total metric.
I'm struggling with creating a DAX measure out of this! Please help!
Here are two tables:
Account Sales
| AccountID | Sales | % of Total | Cumulative % of Total | Decile |
| 362417 | 2000 | 7.6% | 8% | 10 |
| 429178 | 1950 | 7.4% | 15% | 9 |
| 832729 | 1950 | 7.4% | 22% | 8 |
| 467110 | 1900 | 7.2% | 30% | 8 |
| 978383 | 1878 | 7.1% | 37% | 7 |
| 450265 | 1800 | 6.8% | 44% | 6 |
| 143544 | 1600 | 6.1% | 50% | 6 |
| 879999 | 1500 | 5.7% | 55% | 5 |
| 823912 | 1500 | 5.7% | 61% | 4 |
| 323474 | 1300 | 4.9% | 66% | 4 |
| 443373 | 1200 | 4.5% | 70% | 3 |
| 332469 | 1200 | 4.5% | 75% | 3 |
| 267348 | 1100 | 4.2% | 79% | 3 |
| 782923 | 1000 | 3.8% | 83% | 2 |
| 967447 | 900 | 3.4% | 86% | 2 |
| 559046 | 850 | 3.2% | 90% | 2 |
| 622490 | 800 | 3.0% | 93% | 1 |
| 586494 | 750 | 2.8% | 95% | 1 |
| 203653 | 600 | 2.3% | 98% | 1 |
| 712772 | 600 | 2.3% | 100% | 1 |
Lookup table:
| Lookup Value | Decile |
| 0 | 10 |
| 0.1 | 9 |
| 0.2 | 8 |
| 0.3 | 7 |
| 0.4 | 6 |
| 0.5 | 5 |
| 0.6 | 4 |
| 0.7 | 3 |
| 0.8 | 2 |
| 0.9 | 1 |
| 1 | 0 |
6 Replies
I am unsure as to whether you are asking for the decile measure or everything apart from the AccountID and Sales.
If it's everything here goes:
In Power Query, order the table by Sales descending.
Add an Index Column from 1.
Close and Apply.
Create 3 columns as follows:
2Col% of Tot = AccountSales[Sales]/ CALCULATE(SUM(AccountSales[Sales]), ALL(AccountSales)) 2Cumu%Total = CALCULATE(SUM(AccountSales[2Col% of Tot]), FILTER(AccountSales, AccountSales[Index] <= EARLIER(AccountSales[Index]))) 2Decile = CALCULATE(MIN(TableLookup[Decile]), FILTER(TableLookup,TableLookup[Lookup Value] < AccountSales[2Cumu%Total] ) )You can format the columns to show the appropriate decimal places and as percent
Thank you both for taking the time to help!
Ultimately, I would love for this to be dynamic, meaning that I would be able to select different Time Periods and they decile would automatically update. In that case, would I be able to start with a calculated table as my VAR in the measure? Would I be able to do the sorting of a calculated table within a measure?
Hi,
My solution is completely dynamic.
