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 to add a column but is not possible because sum of measure is not permitted.
In Fact A, B and C are yet a measures.

I can try function CALCULATE but I don't know the correct usage for this issue.

May yuo have any suggestions?

 

Thanks in advance

Franco

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Hi fpavan76,

 

what is the error you are having? I have made a small change in the example I have set-up and created 3 measures for A, B and C and added them to the initial measure I created, and the result is still the same in the column it doesn't give me any error.

 

 

This are simple sum measure and without complex data behind, no filter or nothing. What is the error you are getting? What type of measures are you using.

 

Regards,

MFelix

  • 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

     

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

    Hi fpavan76,

     

    Could you please tell me if my solution works?

     

    Best Regards!

    Dale

  • fpavan76's avatar
    fpavan76
    Regular Visitor
    9 years ago

    Hi v-jiascu-msft

    I've tried to use your solution but return a non-correct results.
    I think it's depend by the model because I've a facts table, many dimension tables and a dates table.

     

    I don't have any idea to solve this issue ..

     

    thanks,

    Franco 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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
    Icon for Microsoft Employee rankMicrosoft 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