Change one value into negative in the Measure

[Anonymous]

Hello

I'd like to prepare a variance waterfall report between two months but I need to show "sales amount's" change as negative compared to other expenses. My measure is very simple comparison one but I do not know how to change only Sales' variance.

Thanks in advance.

Variance =

var Prev_Month = CALCULATE(SUM(PnL[USD_Amount]),ALLEXCEPT(PnL[PnL_Name]),PARALLELPERIOD('Calendar'[Date],-1,Month))

var Curr_Month = CALCULATE(SUM(PnL[USD_Amount]),ALLEXCEPT(PnL_Names,PnL_Names[PnL_Name]))

Return

Curr_Month - Prev_Month

[Anonymous]

Hi @v-yiruan-msft 

I was planning to use a Dax formula to convert one of the values into negative however one of my colleagues simply ask me to create an additional column to only reverse Sales Amount for specific report. 

As my report is not huge, a simple approach solved my problem at the moment. However I would be glad if there is any dax formula to help on huge files.

Best Regards,

[v-yiruan-msft]

Hi @Anonymous ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

And if you still want to get the help, please provide the information which refer in my previous post. I will give you a suitable solution shortly.

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

If I understand correctly, you would like to change the value as negative one when it is 'sales amount'. Am I right? How can we identify that is sales amount or other type amount? Could you please provide some raw(fake) data in your tables 'PnL' and 'PnL_Names' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? Is there any relationship created between these two tables? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards