Forum Discussion

Derf95's avatar
Derf95
Frequent Visitor
9 years ago

Wrong Totals Using IF and Values()

Hi,    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 w...
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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.:smileyhappy:

 

Regards

Derf95's avatar
Derf95
Frequent Visitor
9 years ago

Hi, v-ljerr-msft, 

 

Thanks for your reply!

Here is a link to one drive where i have posted the test file: https://1drv.ms/u/s!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

  • Derf95's avatar
    Derf95
    Frequent Visitor
    9 years ago

    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.