Главная
Study mode:
on
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
Description:
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.

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

ExcelIsFun
Add to list
#Business #Business Software #Microsoft Office 365 #Microsoft Excel #Data Science #Data Analysis #Business Intelligence #DAX (Data Analysis Expressions) #Spreadsheets #PivotTables
0:00 / 0:00
1 of 17

) Introduction. Look at Finished Data Model PivotTable. Look at Established Data Model.