RANKX DAX Function and More - Ranking Profit for Products within Manufacturer

Dive into an in-depth tutorial on using the RANKX DAX function in Excel for ranking profits within product categories and manufacturers. Learn how to create and manipulate data models, understand filter contexts, and apply advanced DAX formulas. Master techniques for ranking total and unit profits, handling potential pitfalls, and utilizing functions like ALL, ALLEXCEPT, and CALCULATE. Explore the intricacies of context transition and discover how to format PivotTables for clear data presentation. By the end, gain comprehensive knowledge to effectively rank and analyze product profitability across different manufacturers in Excel.

) Introduction. Look at Finished Data Model PivotTable. Look at Established Data Model..
) Create Total Profit Formula, start PivotTable..
) Discuss how Filter Context works in the Data Model..
) Start Discussion of how the RANKX Function works.
) DAX Formula to Rank Total Profits for Products: RANKX(ALL(dProduct),[Total Profit]).
) Discussion of last three arguments in RANKX, arguments that are not mandatory if you know what the default behavior is..
) 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..
) 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..
) Use IF and HASONEVALUE to display a BLANK() in the Grand Total Cell.
) When we drop Manufactory 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..
) Format PivotTable.
) 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..
) DAX Formula Rank Unit Profits for Products :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALL(dProduct),dProduct[Unit Profit],VALUES(dProduct[Unit Profit]))).
) 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..
) Close up of how third argument in RANKX, Values argument, works..
) DAX Formula to Rank Unit Profits by Product within Manufacturer :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALLEXCEPT(dProduct,dProduct[Manufacturer]),CALCULATE(SUM(dProduct[Unit Profit])))).
) Summary .