Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Stratifying into 1/3rds Formula

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 reven...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

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.