Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Wrong Totals Using IF and Values()



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])


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


All tips are appreciated!












Hi @Derf95,


That's a little weird. The logic of your measures seems all right. Could you share a sample pbix file which can reproduce the issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy



Hi, @v-ljerr-msft


Thanks for your reply!

Here is a link to one drive where i have posted the test file:!AtXhBVThw98Ja07SGd9hSLPNGxo 
There are a few extra measures in the file, but the ones that should be focused on are the ones that i mentioned in the first post. In the report view you may switch from year 2015 to 2016 to see how newSales/lostSales work/Dont work. If you have any tips on changes i can make in order for the lostSales to show and sum up the right values, that would be great. 


Best Regards

Frequent Visitor

After some more testing i have added the following formula to the table: 


CustomerNameCount = COUNTROWS(VALUES(SalesData[CustomerName]))


As you can see in the image below, it seems that the Values() method doesn´t work unless there exists a SalesYTD value. 

I just wanted to add that in case it can help someone figure out a solution. CustomerCount.png



Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors