Waterfall Chart With Variances Across Multiple Years

[dbattin4]

I have the following data with the variances created by measures. I want to show the vaiances in a waterfall table but when I do this the variances across the two years are being added together. I am using a switch measureand a variance measre shown below. Can you help resolve this?

Selected Measure =

SWITCH(

    TRUE(),

    SELECTEDVALUE(SalesVarianceTable[Category])= "Volume", [Volume Variance Old],

    SELECTEDVALUE(SalesVarianceTable[Category])="Price", [Price Variance],

    SELECTEDVALUE(SalesVarianceTable[Category])="Frequency", [Frequency Variance],

    [Total Sales]

)

Price Variance =

VAR CurrentYear = SELECTEDVALUE(Invoices[DocFinYear])

VAR PreviousVolume = CALCULATE(Sum(Invoices[Quantity]),Invoices[DocFinYear]=CurrentYear-1)

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] = CurrentYear - 1)

RETURN

IF(

    NOT ISBLANK(PreviousASP),

    (CurrentASP-PreviousASP)*PreviousVolume,

    BLANK()

)

[v-hjannapu]

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.

[v-hjannapu]

Hi @dbattin4,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[dbattin4]

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

[rajendraongole1]

create another measure and place in your y-axis.

Selected Measure =
VAR _Category = SELECTEDVALUE(SalesVarianceTable[Category])
VAR _Year = SELECTEDVALUE(Invoices[DocFinYear])
RETURN
SWITCH(
TRUE(),
_Category = "Volume" && NOT ISBLANK(_Year), [Volume Variance Old],
_Category = "Price" && NOT ISBLANK(_Year), [Price Variance],
_Category = "Frequency" && NOT ISBLANK(_Year), [Frequency Variance],
NOT ISBLANK(_Year), [Total Sales],
BLANK()
)

[dbattin4]

@rajendraongole1 Still getting the same answer

[rajendraongole1]

Hi @dbattin4  -Selected Measure is returning the correct variance values per year, but the Waterfall visual is aggregating them when the X-axis has more than one year in scope.

Selected Measure =
VAR _Year = MAX( Invoices[DocFinYear] )
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE(SalesVarianceTable[Category]) = "Volume",
CALCULATE([Volume Variance Old], Invoices[DocFinYear] = _Year),
SELECTEDVALUE(SalesVarianceTable[Category]) = "Price",
CALCULATE([Price Variance], Invoices[DocFinYear] = _Year),
SELECTEDVALUE(SalesVarianceTable[Category]) = "Frequency",
CALCULATE([Frequency Variance], Invoices[DocFinYear] = _Year),
CALCULATE([Total Sales], Invoices[DocFinYear] = _Year)
)

try this and let know.

[dbattin4]

@rajendraongole1 This still gives the same answer. I undestand your logic but I wonder if its the visual. The variances are correct it is just looking at the movement in the variances not the absolute value

[rajendraongole1 · Accepted solution]

Thanks for update, @dbattin4 ,  native Waterfall chart in Power BI doesn’t show absolute values for each step unless it’s a “Total” column - it’s designed to show changes from one category to the next.

If your goal is a Waterfall per year showing Volume, Price, Frequency as steps,
the easiest fix is to add a slicer for year so only one year is visible at a time in the waterfall.

Otherwise, the visual will always try to chain them together and you’ll get exactly the “movement” effect you’re seeing.

check and let me know if still issue persist, please share with some sample data. will check it.

[dbattin4]

In the end I created measures for prior year sales and current year sales and turned off the total

[rajendraongole1]

Cool!

[rajendraongole1]

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 

[wardy912]

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!