Calculate Function in a measure doesn't filter all the data out

[giacomoghezzi]

Hi,

I'm new to the community and I'm experiencing an unexpected error in Power BI. I'm working for a client and going through their sales data, which is split by regions.

As I would like to compare the sales volume in one region to the sales volume of all regions except that one across time I've built a line chart visualization with date on the x axis and the following values:

- Values:

Volume A = Calculate([Total Volume],(Sales[Region]="A"))

Volume (Excl. A) = SUMX(Sales,IF(Sales[Region]<>"A",Sales[Total Volume],0))

Now, when I build my line chart, and I add a slicer for the regions to also be able to see how other regions do (with Volume excl.a), I see that Volume A is never 0 as it should (given the filter of the  measure should prevail on the slicer), but rather some small quantities.

Can anyone help me figure out why? 

Thank you very much,

Giacomo

[v-lili6-msft]

hi  @giacomoghezzi 

If you could use this formula as below:

Volume (Excl. A) = CALCULATE(Sales[Total Volume],Sales[Region]<>"A")

or

Volume (Excl. A) = CALCULATE(Sales[Total Volume],FILTER(Sales,Sales[Region]<>"A"))

If not your case, could you please use dummy data to do a simple sample pbix file and share it and your expected output, that will be a great help.

Regards,

Lin

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[giacomoghezzi]

Hi Greg,

Thank you. Unfortunately I cannot post the sample data as it's client data and I've signed a NDA :(, otherwise I would have...

Best,

G

[MFelix]

Hi @giacomoghezzi ,

First of all try to change your measure to:

Volume (Excl. A) = SUMX(FILTER(Sales, Sales[Region]<> "A" ),[Total Volume]))

This may be  related with a variety of issues from missinformation in your database to syntax in formula, to relationships.

Probably you need to go and filter out information what I ussualy do with this cases is place the information in a table and then start to add details, in your case sales region, dates, .... and so on until you get the detail of what is having impact on your calculation.

[giacomoghezzi]

Thank you, 

I'll give that a try!!

[amitchandak]

Does the above reply helps. Else create a sample data and sample output in table format and provide.

make me @

Appreciate your Kudos.

[yogeshmaney]

can you share a snapshot or power bi file

[giacomoghezzi]

Hi Yogeshmaney,

Thanks for your reply. Unfortunately I can't as it's sensitive data :(.

Best, 

Giacomo