topic Re: Total for Top grouping in a matrix not calculating correctly in DAX Commands and Tips

Total for Top grouping in a matrix not calculating correctly

angelo2022 — Wed, 21 Jun 2023 04:33:33 GMT

Hi,

I wonder if someone can help me with the DAX for a Measure.
I have a target field in a Matrix that doesn't seem to be calculating correctly. See below
In the first level grouping the values are correct, but not in the top grouping or for the grand total.

The Target is a running total for the month grouped by Program, so should be the sum of all the Target values in the top level group and for the grand total.

The DAX I'm using is fairly simple. Uses 2 measures.

[TargetWholeNumber] = SUMX(DISTINCT(DimDate[MonthName]), FIRSTNONBLANK(MeasureData[Target], 0))

This is the measure that I'm using in the matrix.

[TargetWholeNumberRunningTotal] = CALCULATE([TargetWholeNumber], DATESYTD(DimDate[DateValue], "30/06"))

Any advise will be appreciated.

Cheers,

Re: Total for Top grouping in a matrix not calculating correctly

Martin_D — Fri, 23 Jun 2023 20:42:04 GMT

[TargetWholeNumber] = SUMX(DISTINCT(DimDate[MonthName]), FIRSTNONBLANK(MeasureData[Target], 0))

Using FIRSTNONBLANK you add only one value per month to the total. Thus, in the grand total rows, the total can only be the sum of one subcatrgory, not of all.

What about wrting

[TargetWholeNumber] = SUM(MeasureData[Target])

instead?

Re: Total for Top grouping in a matrix not calculating correctly

angelo2022 — Mon, 26 Jun 2023 04:20:43 GMT

Thanks for your reply, but using the Sum won't work. The target amount is repeated for each measure values. Using a sum will multiple the target amount. I just need to find a way to sum up the group values and grand total amount.

Re: Total for Top grouping in a matrix not calculating correctly

Martin_D — Mon, 26 Jun 2023 05:45:57 GMT

What about setting a month filter on the visual in addition?