Forum Discussion
DAX Measure not summing correctly
Hi All,
Apologies if this is solved elsewhere.
I have 2 data sets related by date. The first data set contains an Actual amount, the second data set contains a forecast amount.
What I need to do is create a calculated column that ignores the Forecast value if an Actual value is present, but when an Actual value is not present displays the Forecast value. I have done this with the following measure:
Measure = if(ISblank(Sum('Actual'[Act])),Sum(Forecast[Forecast]),sum(Actual[Act]))The issue is that the measure column fails to Sum correctly.
The highlighted value should read the same as the Forecast column, but it appears only the first 3 values are being summed?
is anyone able to assist in highlighting what I need to do to have this measure column calculate correctly.
Cheers
Brad
Hi All,
A little more searching and I found a solution.
Thanks Greg_Deckler.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
the solution I used:
Measure1 = VAR __table = SUMMARIZE('Date',[Date],"__value",[Measure]) RETURN IF(HASONEVALUE('Date'[Date]),[Measure],SUMX(__table,[__value]))
1 Reply
Hi All,
A little more searching and I found a solution.
Thanks Greg_Deckler.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
the solution I used:
Measure1 = VAR __table = SUMMARIZE('Date',[Date],"__value",[Measure]) RETURN IF(HASONEVALUE('Date'[Date]),[Measure],SUMX(__table,[__value]))