Completed
) 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.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
RANKX DAX Function and More - Ranking Profit for Products within Manufacturer
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Introduction. Look at Finished Data Model PivotTable. Look at Established Data Model.
- 2 ) Create Total Profit Formula, start PivotTable.
- 3 ) Discuss how Filter Context works in the Data Model.
- 4 ) Start Discussion of how the RANKX Function works
- 5 ) DAX Formula to Rank Total Profits for Products: RANKX(ALL(dProduct),[Total Profit])
- 6 ) Discussion of last three arguments in RANKX, arguments that are not mandatory if you know what the default behavior is.
- 7 ) 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 e…
- 8 ) 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.
- 9 ) Use IF and HASONEVALUE to display a BLANK() in the Grand Total Cell
- 10 ) 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 Func…
- 11 ) Format PivotTable
- 12 ) 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 DA…
- 13 ) DAX Formula Rank Unit Profits for Products :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALL(dProduct),dProduct[Unit Profit],VALUES(dProduct[Unit Profit])))
- 14 ) 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 H…
- 15 ) Close up of how third argument in RANKX, Values argument, works.
- 16 ) DAX Formula to Rank Unit Profits by Product within Manufacturer :=IF(HASONEVALUE(dProduct[Product]),RANKX(ALLEXCEPT(dProduct,dProduct[Manufacturer]),CALCULATE(SUM(dProduct[Unit Profit]))))
- 17 ) Summary
