Forum Discussion
DAX ALLEXCEPT not keeping total amount when filtering
Hi,
I am trying to keep the aggregated amount when applying a filter with the exception of two filters, but it changes the amount anyway.
I am using the following DAX code:
Total Incrementos 1 = calculate(sum(VW_CAMBIOS_COMPROMISO[CAMBIOS]); ALLEXCEPT(VW_CAMBIOS_COMPROMISO; VW_CAMBIOS_COMPROMISO[FEC_CAMBIO]; VW_CAMBIOS_COMPROMISO[TIPO_CAMBIO]))
I want the sum of 'CAMBIOS' and the only filters that should apply are 'FEC_CAMBIO' and 'TIPO_CAMBIO'
In the next screenshot you can see that the total amount after applying the two filters ('FEC_CAMBIO' and 'TIPO_CAMBIO') is 4.263, which is correct.
But it should also keep the amount for 'TECNICA' but as you can see from the screenshot, when I filter by 'DTD' it sums 4236 instead of 4263:
What I finally want is to show the proportion of the filtered item relative to the total.
I hope someone knows how to solve this.
I have also tried the following code without any success:
TOTAL INCREMENTOS2 = CALCULATE(sum(VW_CAMBIOS_COMPROMISO[CAMBIOS]); ALL(VW_CAMBIOS_COMPROMISO); values(VW_CAMBIOS_COMPROMISO[TIPO_CAMBIO]); values(VW_CAMBIOS_COMPROMISO[FEC_CAMBIO]))
10 Replies
- Vvelarde
Community Champion
- vdburg
Resolver I
Hi,
Yes, it is. But it shouldn´t affect anyway. The slicer is one of the two filters that should apply to both 'selected' and 'Total'. It´s 'Tecnica' that shouldn´t change the amount of 'Total'.
- v-ljerr-msft
Microsoft Employee
Hi vdburg,
Is "TECNICA" also a column from the table "VW_CAMBIOS_COMPROMISO"?
Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we help further investigate on t? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:
Regards
- vdburg
Resolver I
Hi v-ljerr-msft, Vvelarde,
Ok, I made up some sample data to share the file with you:
https://1drv.ms/u/s!AtD1xCJ7-i3LukgqpWWWR29E-n3T
In this case the total shows 95:
And when filtering by tecnica it should keep 95, instead it shows 40: