Forum Discussion

Preeti_Yadav's avatar
Preeti_Yadav
Icon for Resolver I rankResolver I
4 years ago
Solved

IF returning Incorrect totals

Hi .

I am calculating Amount using 

 

Amount=

IF(

FIRSTNONBLANK('HRDataSen DaxLaborDetail'[OTMult],0)=1.00,

SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate]),

(SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate]))/1.5.

My individual row values are returning correct output,however it is giving me incorrect totals.
I tried wrapping it under SUMX but to no avail.

Thanks,
Preeti

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Preeti_Yadav what is the granularity?
    Like each row has a unique value in ADPworklocationdescroption column or it's a combination of also EmployeeID and ApplyDate.
    Anyway try:

    Amount =
SUMX (
    SUMMARIZE (
        'Table Name',
        'Table Name'[ADPworklocationdescroption],
        'Table Name'[EmployeeID],
        'Table Name'[ApplyDate]
    ),
    CALCULATE(
    	IF(
			
			FIRSTNONBLANK('HRDataSen DaxLaborDetail'[OTMult],0)=1.00,
			
			SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate]),
			
			SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate])/1.5
		)
		
	)
)


    Will apprecuiate Your Kudos on my messages 🙂

3 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Preeti_Yadav SUMX should be the solution for that, but need to see how exacty you used it in the visual.
    Can you give more info about how the visual looks like and what are the names of the column in play in that visual.

    • Preeti_Yadav's avatar
      Preeti_Yadav
      Icon for Resolver I rankResolver I
      4 years ago

      Hi   

      below is the provided screenshot , please have a look

       

       

      SpartaBI

       

      regards,

      Preeti Yadav

      • SpartaBI's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Preeti_Yadav what is the granularity?
        Like each row has a unique value in ADPworklocationdescroption column or it's a combination of also EmployeeID and ApplyDate.
        Anyway try:

        Amount =
SUMX (
    SUMMARIZE (
        'Table Name',
        'Table Name'[ADPworklocationdescroption],
        'Table Name'[EmployeeID],
        'Table Name'[ApplyDate]
    ),
    CALCULATE(
    	IF(
			
			FIRSTNONBLANK('HRDataSen DaxLaborDetail'[OTMult],0)=1.00,
			
			SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate]),
			
			SUM('HRDataSen DaxLaborDetail'[WorkedHours])*SUM('HRDataSen DaxLaborDetail'[CalcRate])/1.5
		)
		
	)
)


        Will apprecuiate Your Kudos on my messages 🙂