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

 

  • 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.

     

8 Replies

  • 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

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

      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?