Convert SQL Calculations into DAX - MMC, Average, Discount, Average Discount

[min-E]

Hello,

I have some calculations that work in sql but I cannot figure out in PBI. 

Thank you so much for your help.

-MMC

mmc = SUMX('mytable',[qty]*[price])

-AvgMMC

-MMDisc

-AvgDisc

SQL

select 
item
, sum(qty) as qty, sum(qty*Price) as mmc
, case sum(qty*Price) when 0 then 0 else sum(qty*Price)/sum(qty) end as avgmmc
, convert(decimal(10,2),sum(qty*round((Price * C_adj) * G_adj,2))) as mmcDisc
, case sum(qty*Price) when 0 then 0 else convert(decimal(10,2),avg(round((Price * C_adj) * G_adj,2))) end as avgDisc

SQL Returns correct info:

item	qty	mmc	avgmmc	mmcDisc	avgDisc
tomato	21	241.5	11.5	224.7	10.7

here is my data

item	qty	price	g_adj	c_adj
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1
tomato	1	11.5	0.93	1

[Fowmy · Accepted solution]

@min-E 

You need to create four measures as follows:

MMC = SUMX(TableName, TableName[Qty] * TableName[Price])

AvgMMC = DIVIDE([MMC], SUM(TableName[Qty])) + 0

MMCDisc = SUMX(TableName, TableName[Qty] * ROUND((TableName[Price] * TableName[C_adj]) * TableName[G_adj], 2))

AvgDisc = IF(MMC = 0, 0, DIVIDE(SUMX(TableName, ROUND((TableName[Price] * TableName[C_adj]) * TableName[G_adj], 2)), SUM(TableName[Qty])))

[AmiraBedh · Accepted solution]

I don't recommend the approach of translating SQL or any other programming/querying language to DAX, but you can proceed like the following :

MMC = SUMX('mytable', [qty] * [price])
AvgMMC = DIVIDE([MMC], SUM('mytable'[qty]))
MMDisc = SUMX('mytable', [qty] * ROUND([price] * [c_adj] * [g_adj], 2))
AvgDisc = DIVIDE(SUMX('mytable', [qty] * ROUND([price] * [c_adj] * [g_adj], 2)), SUM('mytable'[qty]))
AvgDisc Direct = AVERAGEX('mytable', ROUND([price] * [c_adj] * [g_adj], 2))

[min-E · Accepted solution]

Both of these have worked and helped me understand much better how this works. Thank you!!! 

[AmiraBedh · Accepted solution]

I don't recommend the approach of translating SQL or any other programming/querying language to DAX, but you can proceed like the following :

MMC = SUMX('mytable', [qty] * [price])
AvgMMC = DIVIDE([MMC], SUM('mytable'[qty]))
MMDisc = SUMX('mytable', [qty] * ROUND([price] * [c_adj] * [g_adj], 2))
AvgDisc = DIVIDE(SUMX('mytable', [qty] * ROUND([price] * [c_adj] * [g_adj], 2)), SUM('mytable'[qty]))
AvgDisc Direct = AVERAGEX('mytable', ROUND([price] * [c_adj] * [g_adj], 2))

[Fowmy · Accepted solution]

@min-E 

You need to create four measures as follows:

MMC = SUMX(TableName, TableName[Qty] * TableName[Price])

AvgMMC = DIVIDE([MMC], SUM(TableName[Qty])) + 0

MMCDisc = SUMX(TableName, TableName[Qty] * ROUND((TableName[Price] * TableName[C_adj]) * TableName[G_adj], 2))

AvgDisc = IF(MMC = 0, 0, DIVIDE(SUMX(TableName, ROUND((TableName[Price] * TableName[C_adj]) * TableName[G_adj], 2)), SUM(TableName[Qty])))