IF function in DAX PBI-CodePudding

I am trying to translate the following function from Qlikview (text object properties) to DAX PBI:

=if(SUM(TOTAL)>= 400000,

num((((SUM(TOTAL)-400000)*0.0118) 12000),'€ #.##0'),

How do I do this? Thank you.

CodePudding user response：

The formula is incomplete. It doesn't state what you want to return if the condition is not met. The DAX syntax would be

=If(SUM(TableName[Total])>=40000,((SUM(TableName[Total])-40000)*0.0118) 12000,null)

The "null" is returned when the sum of the total is less than 40000. Adjust as required. You can use formatting to apply the desired currency format. In DAX, the format is not applied inside the formula.

Also, you may want to use an initial measure to calculate the total, then re-use that measure in other calculations. It is more efficient.

Create a measure for the total, for e.g.

Total Turnover = SUM(TableName[Total])

Then use that in the formula for the calculation measure

WhateverName = If([Total Turnover]>=40000,(([Total Turnover]-40000)*0.0118) 12000,null)