SUMX measure with two column filters but need to be unaltered by any slicers

[Anonymous]

Hi All,

This is my first post in this forum.

I am stuck with an issue with a SUMX.

My formula is:

CALCULATE( SUM (Data[Sales]) , FILTER(Data , Data[Product] = "A" && Data[Manager] = "Mgr1") )

I have placed this in a Card visual in a page.

This works fine, but the value will change based on any slicer selection made in the page (the page has some slicers)

What needs to be altered in the formula so that the measure value does not change based on slicer selection in the page?

Advance Thanks for any help 🙂

Regards

Krishnakumar

[AlB · Accepted solution]

Hi @Anonymous 

Add an ALL to eliminate the effect of the slicers:

CALCULATE( SUM (Data[Sales]) , FILTER(ALL(Data) , Data[Product] = "A" && Data[Manager] = "Mgr1") )

If the slicers are on columns of a table other than Data, you'll need an ALL on that table too (or rather the colums of that table used in the slicer)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi AIB,

This is exactly what i wanted. Thank you so much.

However when i try "Accept as solution" it shows me some error like below and i dont know why.

Not able to "like" too

"Authentication failed for the action you are trying to do. This failure could be due to your browser not supporting JavaScript, JavaScript not being enabled, or trying to use the action URL directly in the browser address bar instead of clicking the link on the page."

Regards

Krishnakumar