Forum Discussion

nbrandborg's avatar
nbrandborg
Helper II
4 years ago
Solved

Divide based on multiple conditions

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.

CountryMonthRatioYoY % Change
Sweden10,424,76%
Sweden40,214,76%
Sweden80,354,76%
Sweden120,444,76%
Denmark10,07142,86%
Denmark40,03142,86%
Denmark80,21142,86%
Denmark120,17142,86%

 

How can this be done easily?

Best regards!

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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

     

5 Replies

  • nbrandborg's avatar
    nbrandborg
    Helper II
    4 years ago
    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))
     
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      nbrandborg
      Helper II
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

        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