Stratifying into 1/3rds Formula

[Anonymous]

I have around 50 companies and their revenue amounts. I want to create a column or a measure (not sure which would be best) to stratify the revenue amounts into 1/3rds by largest to smallest in revenue. I want the top third (in total revenue amount) to get a risk score of 15, the middle third would get a 10 and the bottom third would get a 5. 

[v-xuding-msft]

Hi @Anonymous ,

I created a simple sample that you could try and check if it is what you want.

They are all measures.

Cumulated Revenue = 
CALCULATE (
    SUM ( 'Table'[Revenue] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Business] <= MAX ( 'Table'[Business] ) )
)

Cumulated Percentage =
DIVIDE (
    [Cumulated Revenue],
    CALCULATE ( SUM ( 'Table'[Revenue] ), ALL ( 'Table' ) )
)

ABC Class = 
SWITCH (
    TRUE (),
    [Cumulated Percentage] <= 0.33,5,
    [Cumulated Percentage] <= 0.66,10,
   15
)

For more details, Please see the attchment. If I misunderstanding, you could reference the DAX patterns blog to have a try.

ABC Classification 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

After I made the cumulated Revenue measure I got the following error message:

MdxScript(Model) (4,101) Calculation error in measure [Cumulated Revenue}: DAX comparison operations do not support comparing values of type Number with values of type Text.

[v-xuding-msft]

Hi @Anonymous ,

Have you resolved the problem? If yes, please accept the helpful answer as a solution. And welcome to share your solution. If you still have questions, please share a few screenshots to make us understand clearly.

[Ashish_Mathur]

Hi,

In the Query Editor, change the format of the numeric column to Decimal numbers.

[v-xuding-msft]

Hi @Anonymous ,

> DAX comparison operations do not support comparing values of type Number with values of type Text.

Based on the error message, it might be caused by the parentheses. You could use this to format it and check it. And can you please share your formula and the data type of columns? Then we can find the error out.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous 

One way is percentile - https://community.powerbi.com/t5/Desktop/Calculating-the-percentile-for-a-set-of-data/td-p/250581

Second is the percent of running total,

Like

measure =
var _1 = divide(calculate(table[revenue],filter(all(table),table[revenue]<=table[revenue])),calculate(table[revenue],all(table)))
return
if(_1<=.33 ,15, if(_1< .66,10,5))

[Greg_Deckler]

Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Probably use RANKX to get a ranking and then you can segment that into thirds. Diffifult to be more specific.

[Anonymous]

Below is some sample data. So the file is two columns (Business and Revenue). 

Business	Revenue
Cubs	344,748
White Sox	193,262
Red Sox	49,997
Padres	45,929
Yankees	155,912
Dodgers	82,667
Mets	79,192
Cardinals	77,123
Braves	74,897
Astros	80,239