Forum Discussion
Table subtotal not excluding blank values
Hi,
I am trying to understand why the subtotal of this table is not ignoring a blank value measure in the matrix.
Look at this example bellow:
The measure Budget D. YTD is summing correctly, also as the measure Earned Value.
For the measure Budget YTD (test) I created a condition that if Earned Value is blank, not show the measure
Budget YTD (test) = IF(ISBLANK([Earned Value]);BLANK();[Budget D. YTD])
But even with this condition above, the subtotal of the table is summing the previous value of Budget D. YTD.
Hi,
Try this measure
=SUMX(SUMMARIZE(VALUES(Data[EVM Index]),Data[EVM Index],"ABCD",[Budget YTD (test)]),[ABCD])
Hope this helps.
5 Replies
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Thanks for the answer Greg_Deckler but didn't work.
I did what you told on the link you mentioned and my measure was like this
Budget YTD (test) =IF(HASONEFILTER('DIM TEMPOS'[Month Year]);IF(ISBLANK([Earned Value]);BLANK();[Budget D. YTD]);SUMX(FILTER('EVM Measures';[Earned Value]<>BLANK());[Budget D. YTD]))Maybe is because my condition on if statement is according to another measure?
I should have doing something wrong.
I don't see a SUMMARIZE or GROUPBY in there so you're likely doing it wrong. It's not just because it is not excluding blanks, it goes way deeper than that. Think about it, in the context of ALL (which is the context of the Total row), [Earned Value] is not blank. [Earned Value] is only blank within the context of the row of your matrix/table "02 Truck Collector...". So, you have to recreate the context of the table/matrix visualization in a table variable in DAX and then aggregate across the rows in that table variable as the article says to do. Otherwise you are not recreating the context properly and will fail. So, unless there is a SUMMARIZE or a GROUPBY in your formula, it is likely wrong.
Hi,
Try this measure
=SUMX(SUMMARIZE(VALUES(Data[EVM Index]),Data[EVM Index],"ABCD",[Budget YTD (test)]),[ABCD])
Hope this helps.
