Forum Discussion
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...
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Awesome this works! Thanks!!!