topic Re: DAX Formula Total is coming 0 in DAX Commands and Tips

DAX Formula Total is coming 0

Tool123456 — Fri, 12 Mar 2021 18:57:42 GMT

Hello Experts, If you could help in correcting DAX Logic

We have created a DAX Logics, based on two measures we have ZActuals & ZForecast.

ZFC = If (SUM(Project[ZActuals])=0,SUM(Project[ZForecast]),0)

If you see below snapshot, the Total for all months for ZFC is coming 0, which is actually not the case. How can we make it show the actual Total. For individual months, the Logic seems to work correctly, but the Total is shoing up 0.

Re: DAX Formula Total is coming 0

themistoklis — Fri, 12 Mar 2021 21:29:17 GMT

Tool123456

Try the following formula:

ZFC = If (SUM(Project[ZActuals])=0, SUMX (VALUES ( Project[Year-Month] ); CALCULATE ( SUM(Project[ZForecast]) )), 0)

Re: DAX Formula Total is coming 0

Tool123456 — Sun, 21 Mar 2021 03:35:10 GMT

Hello

I replied to your post next Day. Not sure if it got deleted or not posted correctly. Apologies.

I tried formula the very next Day, it is leading to the same result. The sum is still coming 0.

Re: DAX Formula Total is coming 0

themistoklis — Sun, 21 Mar 2021 07:38:57 GMT

Tool123456

Can you share the file with us so as to have a look at it?

Re: DAX Formula Total is coming 0

ERD — Sun, 21 Mar 2021 08:23:35 GMT

Hello Tool123456,

You can use the logic described here: Measure Totals, The Final Word.

So your measures will be:

ZFCPerMonth = IF( SUM(ZFC[ZActuals])=0, SUM(ZFC[ZForecast]), 0)

and

ZFC = VAR _table = SUMMARIZE(ZFC, ZFC[Month], "ZFCValue", [ZFCPerMonth]) RETURN IF(HASONEVALUE(ZFC[Month]), [ZFCPerMonth], SUMX(_table, [ZFCValue]))

Re: DAX Formula Total is coming 0

Tool123456 — Sun, 21 Mar 2021 15:36:54 GMT

Hello

Apologies. Might not be able to share the file. It might contain confidential informaiton, which I am not supposed to share. Thanks for trying, you were pretty close. The solution suggested by ERD worked. Thanks

Re: DAX Formula Total is coming 0

Tool123456 — Sun, 21 Mar 2021 15:37:41 GMT

Thanks this Worked.