Wrong Totals Using IF and Values() in Power BI

Question

I am working on getting the amount of new sales and lost sales with regards to sales previous year and sales year to date. I am trying to show this in a table and then filtering that table with a year slicer. Below are the formulas that i have used:

SalesPY = CALCULATE(SUM(SalesData[Value]),SAMEPERIODLASTYEAR('Calendar'[DateKey]))

SalesYTD = TOTALYTD(SUM(SalesData[Value]), 'Calendar'[DateKey])

NewSalesUppdate = SUMX(VALUES(SalesData[CustomerName]),IF([SalesYTD] > 0 && [SalesPY] = 0, [SalesYTD]))

LostSalesUppdate = SUMX(VALUES(SalesData[CustomerName]),IF([SalesYTD] = 0 && [SalesPY] > 0, -[SalesPY]))

LostSalesOld = IF([SalesPY] > 0 && [SalesYTD] = 0, -[SalesPY])

The NewSalesUppdate formula works as it should and sums up correctly. However LostSalesUppdate does not work, despite having pretty much the opposite formula compared with NewSalesUppdate. It seems like the IF statement never becomes true. That is strange because the LostSalesOld formula shows the right value, but it does not show the total.

All tips are appreciated!

Sample Data:

Current Result:

Notice how customer A had no YTD sales. The LostSalesOld shows -85000 in sales, but nothing is reflected in the total. The LostSalesUppdate shows nothing at all.

Desired Result:

Now one of the lost sales columns (doesn't matter which) has a value for customer A, and a total

Can you explain with some sample (say monthly) data what you'd like to accomplish and what you're currently seeing? The SalesPY and SalesYTD measures are different (one is confined to the current date context, the other is a YTD measure). For that reason, SalesYTD would never be 0 once there has been a sale in the current year, whereas SalesPY could frequently be 0. For that reason, I wouldn't describe LostSalesUppdate and NewSalesUppdate as opposite to each other. — Leonard
Thank you for your reply @Leonard ! I will try to be a little more specific. — Derf95
In the Table below you can see how i would like it to work. However, as i said before, the LostSalesOld formula shows -70000 for customer A in lostSales, but does not show the total. When i instead try to use LostSalesUppdated the lostSales column just becomes blank ( never becomes true). So i know that the formula works when i only use the IF statement, but it does not work when i add the Values method. Since i am filtering the SalesYTD on each customer, it feels like it should be possible for SalesYTD to be 0. @Leonard — Derf95
I hope it is clear what i am trying to achieve. Do you have any tips on other formulas that i can use instead in order to get the desired result? CustomerName SalesPY SalesYTD NewSales LostSales A 70000 0 -70000 B 0 50000 50000 Total 70000 50000 50000 -70000 — Derf95
I now realize that the table that i tried to write in the comments field did not turn out that good. I would really appreciate it if you could email me at [email protected], so that it would be easier for me to send images of my table in power bi. That way i think it will be easier to understand. If anyone else would like to help me as well, you are more than welcome to send me an email too. When we have found the answer, we can post it here, so that other people can see it. @Leonard — Derf95

Leonard Leonard · Accepted Answer · 2017-01-30T18:06:38

Focusing on LostSalesUppdate, the issue is one of context. Your measure is saying "For each customer name in the SalesData table, show me last year's sales negated if they had sales last year and none this year".

The problem (and I'll admit it is subtle), is that because there are no sales for Customer A this year, Customer A is not in the SalesData table as far as this measure is concerned. Therefore, the rest of the formula is ignored.

What I would recommend is adding a separate table with a list of customers, similar to your date table (one row per customer). Then, update your LostSalesUppdate formula so that instead of pulling CustomerName from SalesData, it pulls it from your new customer table.

LostSalesUppdate =
SUMX (
VALUES ( Customer[CustomerName] ),
IF ( [SalesYTD] = 0 && [SalesPY] > 0, - [SalesPY] )
)

Wrong Totals Using IF and Values() in Power BI

1 Answers