Reply
Frequent Visitor
Posts: 2
Registered: ‎01-02-2019
Accepted Solution

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.

 

2019-01-31 17_55_26-Window.png

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


Accepted Solutions
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎01-02-2019

Re: DAX Measure not summing correctly

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]))

View solution in original post


All Replies
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎01-02-2019

Re: DAX Measure not summing correctly

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]))