Forum Discussion

Julia_Mav's avatar
Julia_Mav
Helper II
1 year ago
Solved

Help with sorting and classification

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

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.