Forum Discussion
Natty004
4 years agoFrequent Visitor
Calculating % increase as dollar value - DAX
Hello, I'm relatively new to powerBI and I'm trying to calculate the actual $ amount of margin increase compared to the margin % last month, and I'm struggling with the DAX (Therefore as we have ...
Natty004
4 years agoFrequent Visitor
Hello tamerji,
Thanks for your response. I have added the above calculations and measures however the
$ margin increase amount is the same value as margin amount.
See below:
YearMonth Sequential Number =
RANKX (
'Table1',
YEAR ('Table1'[Dt Invoice]) * 100
+ MONTH ( 'Table1'[Dt Invoice] ),
,
ASC,
DENSE
)
Measure
$ MArgine Increase =
VAR CurrentMonth =
MAX ( Table1[YearMonth Sequential Number] )
VAR CurrentMonthRevenue = 'Table1'[Measure : TOTAL Revenue]
VAR CurrentMonthMarginPercent = [Measure : Margin %]
VAR PreviousMonthMarginPercent =
CALCULATE([Measure : Margin %],Table1[YearMonth Sequential Number]=CurrentMonth-1)
RETURN
CurrentMonthRevenue * ( CurrentMonthMarginPercent - PreviousMonthMarginPercent )
Margin $ measure
Measure : Total Margin = Table1[Measure : TOTAL Revenue]-Table1[Measure : Total Oncosts]
I then tried to calculate current margin % and Prior month Matgin % and add these to the matrix to see if they are calculating correctly.
The current margin % measure below matches the same calculation [Measure : Margin %]
Current Month % = CALCULATE(
divide(Table1[Measure : Total Margin],
Table1[Measure : TOTAL Revenue],0),
Table1[YearMonth Sequential Number])Prior month % - does not pull through the previous month - it just duplicates the current month %
Prior Month % =
VAR CurrentMonthMargin = CALCULATE([Measure : Margin %], Table1[YearMonth Sequential Number])
VAR PriorMonthMargin = CALCULATE([Measure : Margin %], Table1[YearMonth Sequential Number] -1)
RETURN
PriorMonthMargin
Apologies in advance, are you able to identfy where I am going wrong?
many thanks in advance