Forum Discussion

mgreez's avatar
mgreez
Advocate II
6 years ago

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...
Need Help
HotChilli's avatar
HotChilli
Community Champion
6 years ago

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