Forum Discussion

dbattin4's avatar
dbattin4
Frequent Visitor
1 year ago
Solved

Waterfall Chart With Variances Across Multiple Years

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...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

wardy912's avatar
wardy912
Icon for Super User rankSuper User
1 year ago

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!