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
Icon for Super User rankSuper User
9 years ago

Hi fpavan76,

 

Just add this Measure to you data:

 

Measure=
     IF (
         SUM ( Table1[A] ) + SUM ( Table1[B] )
             > SUM ( Table1[C] ),
         SUM ( Table1[A] ) + SUM ( Table1[B] ),
        0
     )

 

Regards,

MFelix

fpavan76's avatar
fpavan76
Regular Visitor
9 years ago

Hi MFelix

thanks for your help but I would obtain in line Total the effective sum of table rows..
Do you have any idea?

I've attach an example, in line Total of measure I would like see the sum of rows (1.041)

thanks in advance

 

Regards,

Franco

 

 

 

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

    Is your A + B + C columns a sum of several values or are they individual values in a table? you can add it as a column instead of a measure.

     

    Regards,

    MFelix

    • fpavan76's avatar
      fpavan76
      Regular Visitor
      9 years ago

      A,B and C are a measures sum of several values.

       

      for example:
      A= CALCULATE ( SUM( Table1[A1] ); FILTER ( DimDates;DimDates[Date] ) )

       

      If I add a tabel column I don't think obtain Grand Total a correct result..

       

       

      thanks in advance 

      Franco

       

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

        fpavan76

         

        Hi Franco,

         

        We need a workaround here. In my example, we can see it works.

         

        2Measure =
IF (
    HASONEVALUE ( 'Date'[Calendar Year] ) = FALSE (),
    SUMX ( 'Date', [Sales Amount] ),
    [Sales Amount]
)

         

         

         

         

         

         

         

         

         

         

         

        Maybe your formula is something like this:

        2Measure =
IF (
    HASONEVALUE ( 'Table1'[Column_in_table] ) = FALSE (),
    SUMX ( 'Table1', [Measure] ),
    [Measure]
)

         

        Best Regards!

        Dale