Need to create YoY line metrics based off Transaction Value and Transaction Type

[powerJR88]

Hi,

I have created a bar chart where I have grouped different transactions together into their own categories and put them together in a new column called Transaction Type (groups).

Now, I need to create 4 YoY line metrics based off the group filters in the legend. I have the below measures:

YoY Volume Change Group 1 =

VAR CurrentYear = SELECTEDVALUE('PLI'[Year])

VAR CurrentYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear,

    'PLI'[Transaction Type (groups)] = "Financing Transactions"

)

VAR PreviousYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear - 1,

    'PLI'[Transaction Type (groups)] = "Financing Transactions"

)

RETURN

IF(

    CurrentYear IN {2021, 2022, 2023},

    IF(

        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,

        BLANK(),

        (CurrentYearVolume - PreviousYearVolume) / PreviousYearVolume

    ),

    BLANK()

)

YoY Volume Change Group 2 =

VAR CurrentYear = SELECTEDVALUE('PLI'[Year])

VAR CurrentYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear,

    'PLI'[Transaction Type (groups)] = "Royalty Transaction"

)

VAR PreviousYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear - 1,

    'PLI'[Transaction Type (groups)] = "Royalty Transaction"

)

RETURN

IF(

    CurrentYear IN {2021, 2022, 2023},

    IF(

        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,

        BLANK(),

        (CurrentYearVolume - PreviousYearVolume) / PreviousYearVolume

    ),

    BLANK()

)

YoY Volume Change Group 3 =

VAR CurrentYear = SELECTEDVALUE('PLI'[Year])

VAR CurrentYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear,

    'PLI'[Transaction Type (groups)] = "Service Transactions"

)

VAR PreviousYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear - 1,

    'PLI'[Transaction Type (groups)] = "Service Transactions"

)

RETURN

IF(

    CurrentYear IN {2021, 2022, 2023},

    IF(

        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,

        BLANK(),

        (CurrentYearVolume - PreviousYearVolume) / PreviousYearVolume

    ),

    BLANK()

)

YoY Volume Change Group 4 =

VAR CurrentYear = SELECTEDVALUE('PLI'[Year])

VAR CurrentYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear,

    'PLI'[Transaction Type (groups)] = "Tangible Goods Transfer Transactions"

)

VAR PreviousYearVolume = CALCULATE(

    SUM('PLI'[Converted Value ($USD)]),

    'PLI'[Year] = CurrentYear - 1,

    'PLI'[Transaction Type (groups)] = "Tangible Goods Transfer Transactions"

)

RETURN

IF(

    CurrentYear IN {2021, 2022, 2023},

    IF(

        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,

        BLANK(),

        (CurrentYearVolume - PreviousYearVolume) / PreviousYearVolume

    ),

    BLANK()

)

But if I apply the above measures to the line portion of the line and stacked column chart, it gives me this:

I need for the values to show up in 2021 and that the percentages not be over 100%. I apprciate your help.

[DataNinja777 · Accepted solution]

Hi @powerJR88 ,

To address the issue of values not showing in 2021 and percentages exceeding 100%, adjustments can be made to your DAX measures. For the missing 2021 values, the problem arises when there is no PreviousYearVolume, which causes the measure to return BLANK(). To include data for 2021, the measure should handle cases where PreviousYearVolume is missing by treating it as 0. This ensures the percentage calculation still works correctly, even in the absence of prior year data.

For percentages exceeding 100%, it's essential to ensure accurate calculation of both CurrentYearVolume and PreviousYearVolume, avoiding any unexpected aggregations. Using the DIVIDE() function ensures safe division, preventing errors caused by dividing by zero or missing values.

Here’s an updated version of your DAX measure for Group 1:

YoY Volume Change Group 1 =
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = 
    CALCULATE(
        SUM('PLI'[Converted Value ($USD)]),
        'PLI'[Year] = CurrentYear,
        'PLI'[Transaction Type (groups)] = "Financing Transactions"
    )
VAR PreviousYearVolume = 
    CALCULATE(
        SUM('PLI'[Converted Value ($USD)]),
        'PLI'[Year] = CurrentYear - 1,
        'PLI'[Transaction Type (groups)] = "Financing Transactions"
    )
RETURN
IF(
    NOT(ISBLANK(CurrentYearVolume)), 
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        0, -- Handle missing PreviousYearVolume by returning 0%
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume)
    )
)

This version ensures that the values for 2021 are included and correctly calculates percentages without exceeding 100%. Similar updates should be made for the measures for Groups 2, 3, and 4 to ensure consistency across all transaction types. Testing the updated measures should confirm that they resolve the current issues. If further assistance is needed, feel free to ask.

Best regards,

[powerJR88]

Thanks @DataNinja777. I updated the measures with the code you provided. But 2 of the 2022 percentages still show as over 100%. 

Please help me fix this.

[Anonymous · Accepted solution]

Hi, @powerJR88 

Based on your description, I've created the following sample data:

Update your measures to the following:

YoY Volume Change Group 1 = 
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED('PLI'),'PLI'[Year] = CurrentYear &&
    'PLI'[Transaction Type (groups)] = "Financing Transactions")
)
VAR PreviousYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED('PLI'),'PLI'[Year] = CurrentYear - 1&&
    'PLI'[Transaction Type (groups)] = "Financing Transactions")
)
RETURN
IF(
    CurrentYear IN {2021, 2022, 2023},
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        BLANK(),
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, BLANK())
    ),
    BLANK()
)

YoY Volume Change Group 2 = 
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear&&
    'PLI'[Transaction Type (groups)] = "Royalty Transaction")
)
VAR PreviousYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear - 1&&
    'PLI'[Transaction Type (groups)] = "Royalty Transaction")
)
RETURN
IF(
    CurrentYear IN {2021, 2022, 2023},
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        BLANK(),
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, BLANK())
    ),
    BL

YoY Volume Change Group 3 = 
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear&&
    'PLI'[Transaction Type (groups)] = "Service Transactions")
)
VAR PreviousYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear - 1&&
    'PLI'[Transaction Type (groups)] = "Service Transactions")
)
RETURN
IF(
    CurrentYear IN {2021, 2022, 2023},
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        BLANK(),
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, BLANK())
    ),
    BLANK()
)

YoY Volume Change Group 4 = 
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear&&
    'PLI'[Transaction Type (groups)] = "Tangible Goods Transfer Transactions")
)
VAR PreviousYearVolume = CALCULATE(
    SUM('PLI'[Converted Value ($USD)]),
    FILTER(ALLSELECTED(PLI),'PLI'[Year] = CurrentYear -1 &&
    'PLI'[Transaction Type (groups)] = "Tangible Goods Transfer Transactions")
)
RETURN
IF(
    CurrentYear IN {2021, 2022, 2023},
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        BLANK(),
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, BLANK())
    ),
    BLANK()
)

Here are the results:

If your line chart is still more than 100, use the Table visual to check if the CurrentYearVolume - PreviousYearVolume is greater than the PreviousYearVolume, as shown in the following image:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777 · Accepted solution]

Hi @powerJR88 ,

To address the issue of values not showing in 2021 and percentages exceeding 100%, adjustments can be made to your DAX measures. For the missing 2021 values, the problem arises when there is no PreviousYearVolume, which causes the measure to return BLANK(). To include data for 2021, the measure should handle cases where PreviousYearVolume is missing by treating it as 0. This ensures the percentage calculation still works correctly, even in the absence of prior year data.

For percentages exceeding 100%, it's essential to ensure accurate calculation of both CurrentYearVolume and PreviousYearVolume, avoiding any unexpected aggregations. Using the DIVIDE() function ensures safe division, preventing errors caused by dividing by zero or missing values.

Here’s an updated version of your DAX measure for Group 1:

YoY Volume Change Group 1 =
VAR CurrentYear = SELECTEDVALUE('PLI'[Year])
VAR CurrentYearVolume = 
    CALCULATE(
        SUM('PLI'[Converted Value ($USD)]),
        'PLI'[Year] = CurrentYear,
        'PLI'[Transaction Type (groups)] = "Financing Transactions"
    )
VAR PreviousYearVolume = 
    CALCULATE(
        SUM('PLI'[Converted Value ($USD)]),
        'PLI'[Year] = CurrentYear - 1,
        'PLI'[Transaction Type (groups)] = "Financing Transactions"
    )
RETURN
IF(
    NOT(ISBLANK(CurrentYearVolume)), 
    IF(
        ISBLANK(PreviousYearVolume) || PreviousYearVolume = 0,
        0, -- Handle missing PreviousYearVolume by returning 0%
        DIVIDE(CurrentYearVolume - PreviousYearVolume, PreviousYearVolume)
    )
)

This version ensures that the values for 2021 are included and correctly calculates percentages without exceeding 100%. Similar updates should be made for the measures for Groups 2, 3, and 4 to ensure consistency across all transaction types. Testing the updated measures should confirm that they resolve the current issues. If further assistance is needed, feel free to ask.

Best regards,