topic Bypass an explicit filter to have a global total in DAX Commands and Tips

Bypass an explicit filter to have a global total

Anonymous — Tue, 11 May 2021 20:40:32 GMT

Hi,

I have a view where I show a total of spendings per month. Then I divide this total in four categories in the purpose of separate spendings less than, equal to (not remarquable), without comparision with or greater than the previous month.

My objective is to show uniquely the values where the variation to the previous month is greater than 1% of the total of the current month. For instance, the first line of the category (Inferiores mas de 10% al mes anterior) is correct because the variation is greater than the total (30 970 000 / 100 = 309 700) but the fourth one is not supposed to appear. ((616 000 - 501 000) < 309 700)

My measure for the total global is this :

Mes actual general = CALCULATE([Mes actual], ALLSELECTED('Facturas Proveedores'))

My issue is that when I use my measure "Total mes actual" which is supposed to be global, I have the total of the filtered category. I know that it is the normal behaviour of ALLSELECTED because we cannot remove the explicit filters however I would like to find a way to get this global value, either with DAX or modeling or any proposition you can make.

Thanks !

Re: Bypass an explicit filter to have a global total

wdx223_Daniel — Wed, 12 May 2021 03:05:31 GMT

could it work to add a filter for that view, using a measure such as [actual]-[premonth]>=[global total]/100

Re: Bypass an explicit filter to have a global total

Anonymous — Wed, 12 May 2021 16:09:14 GMT

Thank you for your reply.

Finally I think I found a solution on this topic :
https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563

I created two measures "1% complient" and "1% not complient", a disconnected table "Show or not" which I use as a slicer and the last measure "Measure to Show" which I use as a filter in my different matrix.

It is a little bit tricky but the topic explains how to do it very clearly.