DAX Function not Returning expected values (returns blanks)

Question

Hi,

I have a database into which I load financial results. I have table (PBSdata) that contains a field called [Scenario] and this determines if the data you are looking at is Actual, Budget, Forecast, and so on. The transaction amount is field [Amount].

I have the following formulas:

AmountPBS:=CALCULATE(SUM(PBSdata[Amount]))

AmountPBSActToBud:=(CALCULATE([AmountPBS], PBSdata[Scenario]="Budget")-CALCULATE([AmountPBS], PBSdata[Scenario]="Actual"))*[ExpenseIncreaseNegativeLocalMult]

These formulas seem to work most of the times, but it appears that if there are NO rows in a particular filtered situation, then the formula returns a blank.

I have tried wrapping the CALCULATE in an IF statement but I cannot get it to work:

AmountPBS:=IF(ISBLANK(CALCULATE(SUM(PBSdata[Amount]))),0,CALCULATE(SUM(PBSdata[Amount])))

How would you go about ensuring that 0 (zero) is returned in place of a blank?

The reason I need to do this is shown in the attached image. As you can see the AmountPBSActToBud is calculated ONLY when there are transactions (see highlighted cells). You will see that row with no count are not caclulated, however at totals the calculation is correct.

Answer 1

Hi,

Share the download link of the Excel file.