[SOLVED] IF function in DAX PBI

Issue

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.

Solution

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)

Answered By – teylyn

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.