From here, you can see why the Grand Total is ignored: it deals with more than one product. It will only yield an output for those pivot table cells that have one and only one Product in context.
The idea here is that we are checking the current filter context for how many Products are currently active in the cell that is evaluating the expression. The output will be the same in both expressions. With PowerPivot 2.0 (Currently in RC0), you can use the HASONEVALUE function to make the expression even more readable: You could create the following DAX measure to display the value for each product, and avoid any output at the total level:Īs you can see, the pivot table ignores the grand total even if it was configured to show one. IF( COUNTROWS( VALUES( Table1 ) ) = 1, )įor example, if you have a measure that should not be aggregated as in the table below: In general, it has become a standard practice to use the following expression if you want to avoid computing it at the ‘Grand Total’ level:
Thanks to SQL Server MVP Frederik Vandeputte ( twitter) for the topic! 🙂 As I think this is a fairly simple topic but one that can be a bit confusing, I hope this post can help others dealing with the very same issue.
Grandtotal one word how to#
Recently I participated on a thread on the PowerPivot MSDN forums in which it was asked how to detect the level you are on in the pivot table hierarchy in order to conditionally handle the output of a measure.
0 kommentar(er)
