Help with sorting and classification

[Julia_Mav]

Hi all 🙂

Could you help me move this logic from Excel to Power BI? I need to filter products into type I or II using the following process:

• Sort revenue from highest to lowest.
• Calculate the total sum of revenue.
• Calculate 80% of the total revenue.
• And if a product's revenue is part of the top 80%, it's classified as Type I; otherwise, it's Type II.

Thanks!

Illustration below also helps in understanding the request. Sorry instead 3800, should be 4080

[Anonymous]

Hi @Julia_Mav ,

1. you can click transform data to go into power query editor, select sorting method and apply it.

2. you can create measures to calculate the total revenue and 80% of the total revenue respectively.

Total Revenue = SUM('Table'[Revenue])

Revenue Target = [Total Revenue] * 0.8

3. You can create calculation columns for categorization.

Column = 
VAR _sum =
    SUM ( 'Table'[Revenue] )
VAR _80 = _sum * 0.8
VAR _rev =
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        FILTER ( 'Table', 'Table'[Revenue] >= EARLIER ( 'Table'[Revenue] ) )
    )
RETURN
    IF ( _rev <= _80, "I", "II" )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ryan_mayu · Accepted solution]

@Julia_Mav 

pls see the attachment below