Divide based on multiple conditions

[nbrandborg]

Hi all

I struggle to do a YoY comparison based on some criterias
I only need the % comparison to happen between month 1 and 12 as the months are rolling- Month 4 and 8 are irrelevant.
I have a dataset looking a bit like this and I would need either a column or measure that gives be the column marked with bold.

Country	Month	Ratio	YoY % Change
Sweden	1	0,42	4,76%
Sweden	4	0,21	4,76%
Sweden	8	0,35	4,76%
Sweden	12	0,44	4,76%
Denmark	1	0,07	142,86%
Denmark	4	0,03	142,86%
Denmark	8	0,21	142,86%
Denmark	12	0,17	142,86%

How can this be done easily?

Best regards!

[amitchandak]

@nbrandborg ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[nbrandborg]

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?

[PaulDBrown · Accepted solution]

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

[nbrandborg]

That was a very clever solution! Thank you. It works.

[nbrandborg]

I have tried to test the first part of the expression with the following, but it gives me the error: "Too many arguments were passed to the DIVIDE function. The maximum argument count for the function is 3." I think there must be an easier way around it?

PRatioYoY =

DIVIDE(SUM('Monthly Sales (Product Level)'[PRatio]),FILTER(ALLEXCEPT('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[Country]),'Monthly Sales (Product Level)'[MonthNumber]=12),SUM('Monthly Sales (Product Level)'[PRatio]),FILTER(ALLEXCEPT('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[Country]),'Monthly Sales (Product Level)'[MonthNumber]=1))