Assigning values based on running total and ranking within categories

[Danni2]

Hello ,

I have a table below and I want to Calculate revenue for each category, rank products based on revenue( within each category) then identify products within each the category that represent 20% with the highest revenue and assign it a value as "A", products that fall in the in the next 30% assign value as B and the rest assign value as C.  I've added a sample table with desired output . Any help would be much appreciated.  Thanks! 

Product	Revenue	Category	Desired output
tea	10	Food	A
coffee	6	Food	A
orange	5	Food	B
pear	4	Food	B
chips	3	Food	B
soda	2	Food	C
coke	2	Food	C
eggs	1.5	Food	C
bread	1	Food	C
sugar	0.8	Food	C
makeup	10	Other	A
pants	6	Other	A
shoes	5	Other	B
jeans	4	Other	B
scarf	4	Other	B
perfume	3	Other	C
dress	2	Other	C
skirt	2	Other	C
suit	1	Other	C
jacket	1	Other	C

[v-jialongy-msft]

Hi @Danni2 

First, you need to calculate the total revenue for each category.

Total Revenue by Category = 
CALCULATE(
    SUM('Table'[Revenue]),
    ALLEXCEPT('Table', 'Table'[Category])
)

Next, rank the products based on their revenue within each category.

Revenue Rank = 
RANKX(
    ALLSELECTED('Table'[Product]),
    CALCULATE(SUM('Table'[Revenue])),
    ,
    DESC,
    Dense
)

Now, calculate the percentage for each product within its category.

Percentage = DIVIDE(SELECTEDVALUE('Table'[Revenue]),[Total Revenue by Category])

Finally, use the percentage to assign each product to the correct group "A", "B", or "C".

Revenue Category = 
VAR CumPercent = [Percentage]
RETURN
    IF(
        CumPercent <= 0.20, "A",
        IF(
            CumPercent <= 0.50, "B",
            "C"
        )
    )

Best Regards,

Jayleny

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

[Danni2]

Hi @v-jialongy-msft 

Thank you, I tried it but it is not ranking corectly, see below.  Apple should be marked as A and looking at total revenue category output the revenue looks the same for the whole category.  Just to confirm the solution includes 4 measures ? If so how does revenue rank get pulled into the output of other measure ? Thanks