Hey everyone, I am new to powerBI and have been struggling with creating the DAX for the following SQL - the SQL gives the right results:
select
r.customer,
cast('1-' + r.[Month] as date) [date],
sum(cast(replace(r.cost,',','') as int)) revenue,
sum(ct.cost) cost,
(sum(cast(replace(r.cost,',','') as int)) - sum(ct.cost)) / sum(cast(replace(r.cost,',','') as int)) contribution_margin,
(sum(cast(replace(r.cost,',','') as int)) - sum(ct.gross_cost)) / sum(cast(replace(r.cost,',','') as int)) gross_margin
from revenue r
left join
(
select
c.customer,
cast(c.[date] as date) [date],
sum(c.cost) cost,
sum(sj.gross_cost) gross_cost
from
FactCosts c
left join
(
select
cc.customer,
cast(cc.[date] as date) [date],
sum(cc.cost) gross_cost
from
FactCosts cc
where cc.department = 'SOC' or cc.department = 'IR'
group by
cc.customer,
cast(cc.[date] as date)
) sj
on c.customer = sj.customer
and cast(c.[date] as date) = sj.date
where c.stage = 'Ongoing'
group by
c.customer,
cast(c.[date] as date)
) ct
on r.customer = ct.customer
and cast('1-' + r.[Month] as date) = ct.[date]
where cast(replace(r.cost,',','') as int) > 0
group by
r.customer,
cast('1-' + r.[Month] as date)
;
I have managed to create the following in DAX ( Where I have separated the complexity of adding gross_costs and used that as a table in the calculation).
FACT Margin =
ADDCOLUMNS (
SUMMARIZE (
'FACT Revenue',
'FACT Revenue'[date],
'FACT Revenue'[Customer]
),
"Contiburion Margin", CALCULATE ( (SUM ('FACT Revenue'[Revenue]) - SUM('FACT Cost'[cost] )) / SUM ('FACT Revenue'[Revenue]),
FILTER('FACT Cost', 'FACT Cost'[stage] = "Ongoing")),
"Gross Margin", CALCULATE ( (SUM ('FACT Revenue'[Revenue]) - SUM('gross_cost'[gross_cost]) ) / SUM ('FACT Revenue'[Revenue]) )
)
But I am stuck as the results I get are all the same for all customers and I want to group it by customer ( So the Contribution, Gross Margin and Costs are all calculated per customer)
Any suggestions or ideas or any DAX implementations for the sql will be much appreciated.
