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.

     

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?