Total of "visible" values

[Anonymous]

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 🙂

[harshnathani]

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)

[amitchandak]

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

New measures like this should work

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

[Anonymous]

Thank you @amitchandak 

However, they are not measures...

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

[amitchandak]

@Anonymous , Try like

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

[Anonymous]

@amitchandak 

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