Waterfall Chart With Variances Across Multiple Years

Hi dbattin4 

 looks like your problem is in the price measure

VAR CurrentYear = SELECTEDVALUE(Invoices[DocFinYear])

This is looking for a single year

Try using MAX and MIN instead of SELECTEDVALUE

Price Variance =
VAR CurrentYear = MAX(Invoices[DocFinYear])
VAR PreviousYear = CurrentYear - 1
VAR PreviousVolume =
    CALCULATE(
        SUM(Invoices[Quantity]),
        Invoices[DocFinYear] = PreviousYear
    )
VAR CurrentASP =
    CALCULATE(
        DIVIDE(SUM(Invoices[LineTotal]), SUM(Invoices[Quantity])),
        Invoices[DocFinYear] = CurrentYear
    )
VAR PreviousASP =
    CALCULATE(
        DIVIDE(SUM(Invoices[LineTotal]), SUM(Invoices[Quantity])),
        Invoices[DocFinYear] = PreviousYear
    )
RETURN
IF(
    NOT ISBLANK(PreviousASP),
    (CurrentASP - PreviousASP) * PreviousVolume,
    BLANK()
)

 I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

Hi dbattin4  - Visual is grouping them incorrectly before plotting.it can be fix for each variance measure only return a value for its specific category, and BLANK() for everything else. Right now your variance measures like Price Variance don’t have that filter dependency. check the below measure suggested by wardy912 . Since each variance measure now self-filters to its category, Power BI won’t sum unrelated categories together for the same year.

Hope the above suggesion helps. please check and let us know 

Thank you rajendraongole1  and wardy912 . The variances are being calculated correctly. Its the chart that is adding the variance for the two years together and displaying them incorrectly. So for price Variance Bar between 2023 and 2024 its taking 2,318 and subtracting it 9,765 to give 7,447 when it should be just 9,765

Hi dbattin4,

Thank you  for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank rajendraongole1 for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I checked your case and the issue isn’t with your DAX formulas  they are fine. The native Power BI Waterfall visual works in a way that when more than one year is on the X-axis, it links them together and shows the change between them instead of the actual values for each year.

If you want each step Volume, Price, Frequency to show absolute values for one year, you can create separate measures for prior year sales, current year sales, and the variances, then use a small category table for the steps and keep the slicer filtered to a single year. This will stop the visual from chaining the years. Your method of using prior and current year measures with total turned off is also a valid way to fix it.

I tested it with my sample data, and it worked fine. Please find the attached Pbix for your reference.
Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.