Report Ranking based on 2 independent fields in 2 connected tables to concatenate rank and report

[HappyCanuck]

Hi everyone, I have been stuggling with ranking on two different fields to create 1 end result visual

1. Raw data for "table" comes from 'Sales Table' linked to 'Region' table and 'Item' table

2. I need to subtotal regions and items and classify as A or B

3. I need to bring that classification back to the table

4. I than need to create a visual which splits the data between A and B by Region and Item

Assume I need an advanced PowerBI super user guidance (if this is even possible)

Attached image for reference.

And of course as regions or items are added or removed all the calcs update automatically

[v-linhuizh-msft]

Thanks for the reply from FarhanJeelani.

Hi @HappyCanuck ,

Based on your description I created simple data:

Please try the following steps:

1.Create measures:

Measureregion = CALCULATE(SUM('Sales'[Amount]),ALLEXCEPT('Sales','Region'[Region]))

Rankregion = IF(HASONEVALUE('Region'[Region]),RANKX(ALLSELECTED('Region'[Region]),[Measureregion]))

Measureitem = CALCULATE(SUM('Sales'[Amount]),ALLEXCEPT('Sales','Item'[Item]))

Rankitem = IF(HASONEVALUE('Item'[Item]),RANKX(ALLSELECTED('Item'[Item]),[Measureitem]))

2.Create a new column, since I don't know how your status percentage is calculated, I'll use the results of rank for now, and you can modify it to suit your situation

AB = 
VAR _a=IF([Rankregion]<=2,"A","B")
VAR _b=IF([Rankitem]<=2,"A","B")
RETURN
_a&_b

3.Result:

Best Regards,
Zhu

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

[HappyCanuck]

Thanks!!

Is there a way to do this while eliminating the item and region table?

So it would mean adding the

1. Measureregion

2. Rankregion

3. Measureitem

4. Rankitem

added to these three columns in the same table?

[FarhanJeelani]

To achieve this classification and ranking in Power BI, follow these steps:

Step 1: Create Subtotals for Regions and Items
1. In Power BI, use DAX to calculate subtotal measures for your `Sales Table`, `Region Table`, and `Item Table`.
- For example, create a measure for region subtotal:

RegionSubtotal = CALCULATE(SUM('Sales Table'[SalesAmount]), ALLEXCEPT('Sales Table', 'Region'[RegionID]))

- Similarly, create a measure for item subtotal:

ItemSubtotal = CALCULATE(SUM('Sales Table'[SalesAmount]), ALLEXCEPT('Sales Table', 'Item'[ItemID]))

Step 2: Rank and Classify Regions and Items
1. Use RANKX to rank the regions and items based on the subtotals. Here’s an example:

RegionRank = RANKX(ALL('Region'), [RegionSubtotal], , DESC)
ItemRank = RANKX(ALL('Item'), [ItemSubtotal], , DESC)

2. After ranking, create a classification based on the rank or subtotal, marking them as "A" or "B":

RegionClassification = IF([RegionRank] <= 5, "A", "B") // Example for top 5 regions as "A"
ItemClassification = IF([ItemRank] <= 5, "A", "B") // Example for top 5 items as "A"

Step 3: Bring Classification Back to Sales Table
1. Use RELATED or LOOKUPVALUE to bring classifications from `Region` and `Item` tables to the `Sales Table`.

RegionClassInSales = RELATED('Region'[RegionClassification])
ItemClassInSales = RELATED('Item'[ItemClassification])

Step 4: Create Visual Splitting Data Between A and B by Region and Item
1. Use a Matrix visual in Power BI.
2. Place `RegionClassInSales` and `ItemClassInSales` in the rows or columns of the Matrix visual.
3. Use your sales or subtotal measure as the value.

Step 5: Make Visuals Dynamic with Automatic Updates
Since the classification and ranking are based on DAX measures, Power BI will automatically update the results when regions or items are added/removed. Just make sure that your data model relationships between `Sales Table`, `Region`, and `Item` are correctly set up.

This approach should allow you to dynamically rank and classify regions and items while reflecting changes in real-time. Let me know if you need further clarification on any step!

[HappyCanuck]

Thanks!

What if I only use the one table....so no table for region or item? So just adding the 3 dax measures in the first table?

[v-linhuizh-msft]

Hi @HappyCanuck ,

According to your description I removed the relationship between sales and the other two tables, you just need to change the fields in the original measures formula about Item and Region to the fields in the sales table.

Result:

Best Regards,
Zhu

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

[HappyCanuck]

This is awesome. Everything is now in 1 table which is so much quicker.

I have one last thing...everyone loves the report but hoping to add 4 columns to bring in the actual % rank on items and customers (the cummalative values) to drive the A/B rating. If <=80% than "A" else "B" (instead of the Rank # driving the A/B)....but based on the unique rank of Country and Item.

See columns needed in yellow. (purple is region and blue is item getting details from the summary to the right)

Thanks very much for your support!!

[v-linhuizh-msft · Accepted solution]

Hi @HappyCanuck,

Based on your description, your needs can be realized by creating four new measures:

Cumulative region % = 
VAR CurrentRank = [Rankregion]
VAR Total=CALCULATE(SUM('Sales'[Amount]),ALL('Sales'))
RETURN
DIVIDE(CALCULATE(SUM('Sales'[Amount]),FILTER(ALL('Sales'),[Rankregion] <= CurrentRank)),Total)

Regionjudgement = IF([Cumulative region %]<=0.8,"A","B")

Cumulative item % = 
VAR CurrentRank = [Rankitem]
VAR Total=CALCULATE(SUM('Sales'[Amount]),ALL('Sales'))
RETURN
DIVIDE(CALCULATE(SUM('Sales'[Amount]),FILTER(ALL('Sales'),[Rankitem] <= CurrentRank)),Total)

Itemjudgement = IF([Cumulative item %]<=0.8,"A","B")

Result:

Best Regards,
Zhu
Community Support Team

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

[HappyCanuck]

Awesome this works! Thanks!!!