Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
7 years ago
Solved

Dax Measure IF

Hi,

 

I need to calculate a measure by following this logic:

if the column "Actual or Forecast" has or is equal to "Actual" then calculate the result if true (see below)

if the result is false then put the result if false (see below):

 

Result if true


CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")+[Actual Cost Center]

 

Result if false

 

CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")


I tried with something like this but it gives an error: IF('FT Forecast Q3','FT Forecast Q3'[Actual or Forecast]="Actual" then ........

 

Thanks, 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    7 years ago

    cristianml seems like you had parenthesis at wrong place

     

    Forecast Q3 Cost = IF(MAX('FT Forecast Q3'[Actual or Forecast])="Actual", 
CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+
    CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+
    CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")+
    [Actual Cost Center], 
CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+
    CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+
    CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")
    )

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    cristianml try this

     

    IF(MAX('FT Forecast Q3'[Actual or Forecast]="Actual" ), <true measure>, <false measure> )
    • cristianml's avatar
      cristianml
      Post Prodigy
      7 years ago

      Hi parry2k ,

       

      For some reason It doesn't work :

       

       

      Forecast Q3 Cost = IF(MAX('FT Forecast Q3'[Actual or Forecast]="Actual" ),
      CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")+[Actual Cost Center],
      CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges"))
       
      Regards,

       

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        cristianml seems like you had parenthesis at wrong place

         

        Forecast Q3 Cost = IF(MAX('FT Forecast Q3'[Actual or Forecast])="Actual", 
CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+
        CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+
        CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")+
        [Actual Cost Center], 
CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Payroll")+
        CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Non-Payroll")+
        CALCULATE(SUM('FT Forecast Q3'[Amount]),'FT Forecast Q3'[Category Group]="Capital Charges")
        )