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.

dbattin4's avatar
dbattin4
Frequent Visitor
1 year ago

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

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

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        1 year ago

        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.