Change SQL to DAX

[Anonymous]

Hi.
I have these 2 SQL clauses I need to use in PowerBI measure.

This one I need to add decimals = Rivisumma alv 0% = SUMX ( 'ol', [price] * ( [quantity] ) / (1 +  [alv] /100))
This is how I do it in SQL, but haven't found how to do it in DAX.
SELECT ROUND(SUM(ol.quantity * ol.price / (1 + CAST(ol.alv AS DECIMAL(10, 6)) / 100)), 2) Summa


And this one I have tried multiple IF-statements in DAX, but nothing is working. If ol.price is over 0, then sum ol.quantity. If ol.price is under 0 then decrease from ol.quantity.
SUM(CASE WHEN ol.price > 0 THEN ol.quantity ELSE CASE WHEN ol.price < 0 THEN - ol.quantity ELSE 0 END END) Kpl 

Thanks for any help with these.

[hemantsingh]

Try like this in DAX - 

Rivisumma alv 0% = SUMX ( 'ol', [price] * ( [quantity] ) / (1 +  [alv] /100))

Rivisumma alv 0% = 
VAR A = SUMX ( 'ol', [price] * ( [quantity] )
VAR B = 1+ Divide([alv],100,0)
Return
Divide(A,B)

Measure_2 =
Switch(True(),

ol.price >0, sum(ol.quantity),

ol.price < 0, write the comdition here like above,
0
)

hope this helps.

Regards,

Hemant

[amitchandak · Accepted solution]

@Anonymous , Try like

round(SUMX ( 'ol',divide( [price] * ( [quantity] )*1.0 ,(1 + [alv] /100.0))),2)

Change data type to decimal. You can Avoid round by giving it column/measure tools

SUMX('ol' , Switch(True(), ol[price] > 0 , ol[quantity] , ol[price] < 0 , -1* ol[quantity] , 0 ))

[FarhanAhmed]

For the first one you can change the precision of your column in "Columns Tools" ribbon

2nd one you need to create measure like this.

_Qty = IF(price > 0,SUM(quantity),SUM(quantity)*-1)