Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate Sum with filter

Hello,

 

I am facing an issue with a basic measure I want to create :

 

I have a dimension table that have two values :

Y

N

 

This table is linked (1,n) to my fact table that have a X amount

 

I want a measure that gives the total of X for Y only :

 

-> CALCULATE(SUM(X), DIM_TABLE = Y)

 

The total given is good, but as soon as I cross this measure with the dimension the result is :

 

Y | 1000

N | 1000

Total | 1000

 

But I would expect :

 

Y | 1000

N | 0 (since the measure is filtered on DIM = Y)

Total | 1000

 

Could you tell me how to fix it ? 

Thanks.

 

Romain

Need Help

4 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous  try this!

    CALCULATE(SUM(X), filter(all(dimtable[column]),DIM_TABLE = Y)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello VijayP, thanks for the proposal but same issue 😞

     

    And when I take another field from another dimension, or another field from the same dimension this measure is working...

    So it seems to be wrong as soon as I want to show the measure with the same field I am filtering on...

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 

    I think some sample data / pbix without any classified information is helpful to give right solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes you're right 🙂

       

      So I have this :

       

       

      I want a measure that returns only 3 565 169 673 195 on the NON_MNI row.

       

      I have the following measure 

       

      Top_SRN_WithBPCE_Conso_SRN = CALCULATE([Consommation_1],FILTER(ALL('Dimension Convention'[top_mni]),'Dimension Convention'[top_mni]="NON_MNI"))
       
      And i have all the duplicates on the all rows.... I don't understand why. It should be null for other rows.
       
      Thanks !