Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Grand total with IF Function

I have two tables I'm using in a formula - one is an Actuals table and one is a forecast table. My formula basically says if Actuals are larger than forecast, use Actuals if not use the forecast value. This works for all the individual rows when I look at specific programs but the grand total row is not the sum of all the individual programs. I tried adding a SUMX but I must be doing something wrong. Here's my formula:
CQ Factored ACT = SUMX( 'ACT DATA' , ( IF ( 'ACT DATA' [value] > 'FCST DATA' [value], 'ACT DATA' [value] , 'FCST DATA [value]))

 

So, what I want to see in my grand total is the $1,800 (the total of all the individual programs) but what I get is the $1,750 since the total of FCST is larger than the total of ACT.

 ACTFCST Factored
Program 150 100 100
Program 2900 850 900
Program 3650 800 800
 1,600 1,750 1,800
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous

     

    Then just change the measure to:

     

    Total_Projects =
SUMX (
    ADDCOLUMNS ( Project;"ACT"; RELATED(ACT[ACT]; "FCT"; RELATED ( FRCST[FCST ] ) );
    IF ( [ACT] > [FCT]; [ACT]; [FCT] )
)

    Should work in the same way but you have the connection table as a starting point.

     

    Regards,

    MFelix

     

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    I'm assuming your two tables are related by the Program column and that this as a relation of one to one. Create the following measure:

     

     

    Total_Projects =
SUMX (
    ADDCOLUMNS ( Actuals; "FCT"; RELATED ( FRCST[FCST ] ) );
    IF ( [ACT] > [FCT]; [ACT]; [FCT] )
)

     

    The SUMX depends on a table to return the result of the sum since you have values on two tables actuals and forecast the result is not as expected.

     

    Regards,

    MFelix

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Sorry, I was trying to simplify my explanation by saying only two tables. The ACT and FCST tables both have many rows for each project. There is a third table that they are both linked to that has the unique project ID. They are all connected by the Project ID. 

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi Anonymous

         

        Then just change the measure to:

         

        Total_Projects =
SUMX (
    ADDCOLUMNS ( Project;"ACT"; RELATED(ACT[ACT]; "FCT"; RELATED ( FRCST[FCST ] ) );
    IF ( [ACT] > [FCT]; [ACT]; [FCT] )
)

        Should work in the same way but you have the connection table as a starting point.

         

        Regards,

        MFelix

         

  • Aree's avatar
    Aree
    Resolver I
    8 years ago

    If both your forcast and actual are linked then at the linked table you can create a calculated column that uses an IF to assinged a value to that column using your comparison logic

    Then a sum of that will give you a sum of that result and since this is assinged per project then you can slice that by program.

    However i am assuming that there is a 1:1 relationship between both ProjectID and Actual and ProjectID and Forecast. 

    • MFelix's avatar
      MFelix
      Super User
      8 years ago
      Hi Aree,

      Although your solution works calculated columns add space to your file and low flexibility since they are made at row level. As a best practice a measure is always preferible to a column. If you can make the calculations with a measure you should not use.columns.


      Regards
      MFelix
      • Aree's avatar
        Aree
        Resolver I
        8 years ago

        MFelix

        I agree that it does contribute in some manner to the overall size of your file. Given the number of rows this contribution can be insignificant to immense. 

         

        Cheers Mate !