Excel Magic Trick 1419

RANKX DAX Function & More: Ranking Profit for Products within Manufacturer


See how to use the RANKX Function in DAX. Learn :
(00:15) Introduction. Look at Finished Data Model PivotTable. Look at Established Data Model.
(02:24) Create Total Profit Formula, start PivotTable.
(04:14) Discuss how Filter Context works in the Data Model.
(04:55) Start Discussion of how the RANKX Function works
(05:30) DAX Formula to Rank Total Profits for Products: RANKX(ALL(dProduct),[Total Profit])
(06:47) Discussion of last three arguments in RANKX, arguments that are not mandatory if you know what the default behaviour is.
(08:15) RANKX ranks everything first? Potential Pitfall with RANKX when you use an aggregate function in the expression argument, rather than a Measure. Learn about the “hidden CALCULATE Function” around each measure that performs Context Transition, which converts an existing ROW Context into the equivalent Filter Context and helps RANKX to work correctly.
(09:21 and 10:35) Visual of what happens when you use SUM Function in RANKX without the CALCULATE Function. Visual of why you get a rank of one in every cell of the PivotTable.
(11:36) Use IF and HASONEVALUE to display a BLANK() in the Grand Total Cell
(14:03) When we drop Manufacturer Field from product Table into the Row Area of the PivotTable, our formula does not calculate Rank for ONLY Product. We fix it by putting entire dProduct Table into ALL Function. Discussion of the ALL DAX Function.
(16:09) Format PivotTable
(16:46) DAX Formula to Rank Total Profits by Product within Manufacturer :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALLEXCEPT(dProduct,dProduct[Manufacturer]),[Total Profit])) . Learn about the ALLEXCEPT DAX Function.
(18:39) DAX Formula Rank Unit Profits for Products :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALL(dProduct),dProduct[Unit Profit],VALUES(dProduct[Unit Profit])))
(19:11) How do you use third argument in RANKX, the Value argument? We use a column reference in the Expressions argument (second argument) and then the VALUES function in the Values argument WITH IF and HASONEVALUE functions to assist VALUES to get correct answer.
(22:28) Close up of how third argument in RANKX, Values argument, works.
(23:22) DAX Formula to Rank Unit Profits by Product within Manufacturer :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALLEXCEPT(dProduct,dProduct[Manufacturer]),CALCULATE(SUM(dProduct[Unit Profit]))))
(26:17) Summary


Download “Excel Magic Trick 1416-1420” Excel-Magic-Trick-1416-1420.zip – Downloaded 25 times – 4 MB

Be the first to comment

Leave a Reply