Power BI DAX IF DIVIDE for certain condition

user_15313188 0
Viewed 132

So I have a Matrix with Measure Column, that has value like this

PO Plan
-------------
1.500.000
84.500
2.545.198.500
585.000
1.972.000
77.765.324.200

///'PO Plan' is a Measure with Formula "SUM('PO'[PO_AMOUNT])"

As you can see, I have abnormal value on row 3, and want to 'normalize' by dividing it, only if the value is higher than 999.999.999.

So I want it to look like this

PO Plan
-------------
1.500.000
84.500
254.519,85
585.000
1.972.000
7.776.532,42

The first thing that came to mind is using IF, so I tried

IF(SUM('PO'[PO_AMOUNT]>999.999.999, DIVIDE(SUM('PO'[PO_AMOUNT]),10000), SUM('PO'[PO_AMOUNT]))

But it doesn't work, it's only show loading indicator spinning without any error.

What should I do to achieve this? Using one measure if possible.

dax powerbi-desktop powerbi
2 Answers

Your measure is ok. I think the problem can be in a number separators and you didn't close the sum() function with a bracket, but i guess, it can be just a misprint. Remove separators. Also, you can use a variable - you calculates twice the same value, while with a var only once.

VAR result = SUM('PO'[PO_AMOUNT])
RETURN    
    IF(
       result >999999999
      ,DIVIDE(
           result
           ,10000
       )
      ,result)
    )
user_190423790

Hello Please Check this:

PO Plan = 
VAR Filtered_Table= FILTER('PO',IF('PO'[PO_AMOUNT]>999.999.999,DIVIDE('PO'[PO_AMOUNT],10000), 'PO'[PO_AMOUNT]))
VAR Result = SUMX(Filtered_Table,[PO_AMOUNT])
RETURN Result
user_194690880
Related