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
fpavan76's avatar
fpavan76
Regular Visitor
9 years ago

Hi MFelix

 

I've try use your suggestion but I don't know because it work incorrectly.

 

 

 

In attachment you should see the Measure to calculate A,B and C (i.e. Measure A),
Measure with condition,
measure without condition.

 

If you observe the misure with condition the Grand Total is 28, not 30..


Thanks in advance.

Franco

 

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