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 d...
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

     

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))