Forum Discussion

vdburg's avatar
vdburg
Icon for Resolver I rankResolver I
8 years ago

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's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion

    vdburg

     

    Hi, My first suggestion is review if the slicer is selected  to the end.

     

    Regards

     

    Victor

     

    • vdburg's avatar
      vdburg
      Icon for Resolver I rankResolver 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'.

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion

        vdburg

         

        Can you share the PBIX to review it?.

         

        You can anonymize or delete columns that are confidential.

         

        Regards

        Victor

         

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft 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

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion

        vdburg

         

        Hi, try with:

         

        TotalNT = CALCULATE(SUM('Table'[Total]),ALL('Table'[Tecnica]))

         

        With this the measure ignore if you apply a filter(or slicer) to this column in your page.

         

        Regards

         

        Victor