Forum Discussion

Preeti_Yadav's avatar
Preeti_Yadav
Resolver 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'[CalcRat...
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 🙂

SpartaBI's avatar
SpartaBI
Community 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
Resolver I
4 years ago

Hi   

below is the provided screenshot , please have a look

 

 

SpartaBI

 

regards,

Preeti Yadav

  • SpartaBI's avatar
    SpartaBI
    Community 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 🙂