Forum Discussion

HappyCanuck's avatar
HappyCanuck
Frequent Visitor
1 year ago
Solved

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

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...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

FarhanJeelani's avatar
FarhanJeelani
Super User
1 year ago

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's avatar
HappyCanuck
Frequent Visitor
1 year ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      HappyCanuck
      Frequent Visitor
      1 year ago

      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!!

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.