Forum Discussion
Convert SQL Calculations into DAX - MMC, Average, Discount, Average Discount
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 |
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])))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))Both of these have worked and helped me understand much better how this works. Thank you!!!
3 Replies
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])))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))Both of these have worked and helped me understand much better how this works. Thank you!!!