Forum Discussion

fpavan76's avatar
fpavan76
Regular Visitor
9 years ago

Calculate Grand Total with Conditional

Hi to all, I need a suggest about a measure to calculate grand total with particular condition. I have 3 columns A, B and C I want insert this condition: IF A+B > C then A+B else 0 This formula ...
Need Help
MFelix's avatar
MFelix
Super User
9 years ago

Hi fpavan76,

 

Did you try to put the Measure with condition as a result of a column, as I said before, because when you use it in the measure the context is avalueted.

 

Mfelix

fpavan76's avatar
fpavan76
Regular Visitor
9 years ago

Hi MFelix

 

I've tried your proposal but it result a non-correct value.

Do you think it depend to the data model?

 

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi fpavan76,

     

    It's possible is something related with the way you have your data model, is it possible to have a sample of the data you can make a mock-up data or send it in private message.

     

    Regards,

    MFelix

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi fpavan76,

     

    Please allow me to make some explanation. The picture below is from your post.

    I guess the first column is "DimDate"[Date] in your visual. So, we test it if it's a row or the grand total. Because a row only has one "DimDate"[Date] while the grand total has all the "DimDate"[Date]. If the first parameter of "IF" is true, we just run [Measure A]. Or, we run [Measure A] over all the table and sum it. Please have a try.

    Could you please post your formula if failed?

     

    2Measure =
IF (
    HASONEVALUE ( 'DimDate'[Date] ) = TRUE (),
        [Measure A]
    SUMX ( 'Table', [Measure A] ),
)

     

     

     

     

     

     

     

     

    Best Regards!

    Dale