topic Re: Total of "visible" values in DAX Commands and Tips

Total of "visible" values

Anonymous — Tue, 03 Nov 2020 10:17:08 GMT

Hi,

I have got a table with Sales LY and this year sales looking like:

Month Sales LY Sales TY DIF

Jan 100 150 50

Feb 150 130 -20

Mar 200 50 -150

Apr 100

Total 550 330 -220

I have made the "DIF" column only calculate the difference when Sales TY is not blank. However, the total row calculates the total difference of all the rows, including those with blank Sales TY.

How can I only show the total for the "visible" values in the table? So, the above table should look like:

Month Sales LY Sales TY DIF

Jan 100 150 50

Feb 150 130 -20

Mar 200 50 -150

Apr 100

Total 550 330 -120

Hope you can help 🙂

Re: Total of "visible" values

amitchandak — Tue, 03 Nov 2020 10:56:21 GMT

Anonymous , assuming these are measures [Sales TY] , [Sales LY]

New measures like this should work

if(isblank([Sales TY]), blank(), [Sales TY] -[Sales LY])

Re: Total of "visible" values

Anonymous — Tue, 03 Nov 2020 11:00:00 GMT

Thank you amitchandak

However, they are not measures...

Maybe its easier if i somehow make them into a measure?

Re: Total of "visible" values

amitchandak — Tue, 03 Nov 2020 11:04:37 GMT

Anonymous , Try like

if(isblank(sum(Table[Sales TY])), blank(), Sum(Table[Sales TY]) -sum(Table[Sales LY]))

Re: Total of "visible" values

Anonymous — Tue, 03 Nov 2020 11:20:43 GMT

HI Anonymous ,

You can create 2 measures

Difference = IF(MAX('Table'[Sales TY]) = BLANK(), BLANK() ,SUM('Table'[Sales LY]) - SUM('Table'[Sales TY]))

DIF = SUMX('Table',[Difference])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Total of "visible" values

Anonymous — Tue, 03 Nov 2020 13:10:32 GMT

amitchandak

Hmm... that gives me the same result as the "Dif" column.