Forum Discussion
Divide based on multiple conditions
With this model, containing dimension tables for country and month,
and this measure (with a simple SUM base measure for the ratio):
YoY % Change measure = VAR _1 = CALCULATE ( [Sum Ratio], 'Dim Month'[dMonth] = 1, ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] ) ) VAR _12 = CALCULATE ( [Sum Ratio], 'Dim Month'[dMonth] = 12, ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] ) ) RETURN DIVIDE ( _12, _1 ) - 1I've attached the sample PBIX file
Hi Amit
I'll try to make it more clear.
In my dataset I have the three first columns represented in my example above. I need to calculate the percentage difference between what is the Month = 1 and Month = 12, so I would have the Year over Year change. The calculation has to be per country. So one results for Sweden, one result for Denmark. The imagined results are the fourth column which I need to create either by an new custom column or a measure.
So the calculation are as following for Sweden ((0,44-0,42)/0,42) * 100 = 4,76% YoY increase.
So I need to look up the "country" and "month number" as conditions to do the calculation on the "Ratio".
I hope it makes sense?
With this model, containing dimension tables for country and month,
and this measure (with a simple SUM base measure for the ratio):
YoY % Change measure =
VAR _1 =
CALCULATE (
[Sum Ratio],
'Dim Month'[dMonth] = 1,
ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] )
)
VAR _12 =
CALCULATE (
[Sum Ratio],
'Dim Month'[dMonth] = 12,
ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] )
)
RETURN
DIVIDE ( _12, _1 ) - 1
I've attached the sample PBIX file
That was a very clever solution! Thank you. It works.