Biotech Deciling is Different - DAX HELP

[mgreez]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mgreez]

@Ashish_Mathur @HotChilli 

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?

[Ashish_Mathur]

Hi,

My solution is completely dynamic.

[mgreez]

@Ashish_Mathur 

For example, if I filter any of the AccountIDs out, the results aren't going to refresh based on the new subset of data.

Another example would be where I have a Time dimension and wanted to filter on different Time Periods.

[Ashish_Mathur]

Hi,

Please illustrate.  My solution will work.

[HotChilli]

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