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

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
Super 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
      Microsoft 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

    • MFelix's avatar
      MFelix
      Super User
      9 years ago

      Hi fpavan76,

       

      Try to add a column refering to the measure and use it on your table.

       

      My measure was named Measure2 so my column will be:

      ValidationColum = [Measure2]

       

       

      Tell me if it works with your layout/slicers and filters.

       

      Regards.

      • 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