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.

     

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.

       

      • HappyCanuck's avatar
        HappyCanuck
        Frequent Visitor
        1 year ago

        Awesome this works! Thanks!!!