question

weewer avatar image
weewer asked

Wrong grand total for the calculated measure in DAX (SSAS Tabular 2016)

Hi, all! I have an issue with wrong grand total for calculating measure in DAX. The measure calculates depends of different measure and show it's positive values only: Measure2 = IF(Measure1 > 0, Measure1, NULL) Unfortunately, it doesn't add up correctly the grand total: ![alt text][1] Grand Total (Measure2): 30 <-- wrong value, it should be 100 Because the grand total in DAX calculated as: IF(50 - 10 + 20 - 60 + 30 > 0, 50 - 10 + 20 - 60 + 30, NULL) Is there any way to calculate the grand total in the right way? [1]: https://cdn-standard.discourse.org/uploads/sqlteam/original/2X/a/aa124ccd58c2a2ed000b074c2b8bdb528acbdcf9.png
ssas-tabular
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
tzvikl avatar image
tzvikl answered
Hi there, Perhaps try changing your measure definition to use the SUMX function, that way the calculation will execute by iterating over the table row by row, try something like this: Measure2 = SUMX(, If(Measure1>0,Measure1 , Null) )
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.