![]() RANKX( VALUES( Sales ),, , DESC ) <= CustomerTop20Percent ) )Īgain, the values for the detail rows would be correct but the grand total would be incorrect, as it ignores the state context. VAR CustomerTop20Percent = DISTINCTCOUNT( Sales ) * 0.2 To get, for example, the total sales of the Top 20% of Customers in each state, one might try a single measure: Sales of Top 20% (Incorrect Total) = This situation can also occur when performing a Pareto analysis, where once again the context must be taken into account. MIN(, ) - return the minimum of column 2 and column 3 "Sales LY", - column 3 of the table visual and virtual table ![]() ![]() "Sales",, - column 2 of the table visual and virtual table VALUES( Customers ), - column 1 of the table visual and virtual table TIP: Use CRTL + mouse scroll wheel to zoom the text size It is a multi-step formula: check box (in column J), to the cell the box is linked to, to a hidden sum box (in column A), to grand total sum (located at the. TIP: Select the text to change, then use CRTL + SHIFT + L to rename all occurrences at once NOTES: Use ADDCOLUMNS to create a virtual table (consisting of the columns used in your table) inside an iterator DAX PATTERN NAME: Fix Incorrect Totals NOTE: SUMMARIZE can also be used in place of ADDCOLUMNS, but be aware that when using calculated columns, it can lead to inconsistent results and performance issues.Īnother method is to calculate separate values for the detail and total rows, and to use the HASONEVALUE() or ISINSCOPE() functions to test the context to see if one is on a detail row, e.g.: Minimum 2 (Correct Total) = DAX Patterns - Incorrect Totals-2 1783×1338 442 KB
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |