Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
sgirdha3
New Member

Waterfall SUMX

Hi All

I need some help with calculating weighted probability of return of sales over months and show it in a waterfall visual by summarizing calculations between selected periods (dates) and keeping the functionality to see details at customer levels (drill through). Client wants to skip breakdown by months/dates between 2 dates selected in slicer so I used SUMX in waterfall, but somehow it is coming up correctly.

 

Process Of Calculation (Required in Power BI to have user can slice and dice)

 

  1. Dynamic month wise sales total – based on values selected in slicers and filters
    monthly_sales = CALCULATE(SUM(Sheet1[Sales Amount]), ALLSELECTED(Sheet1),Sheet1[Sales Date] IN VALUES(Sheet1[Sales Date]))

  2. Calculating % of total out of sales amount for each month
    % of total = DIVIDE(SUM(Sheet1[Sales Amount]), [monthly_sales])

  3. Multiply % of total with Probability of Return to calculate Weight Pb of Return ( measure with row level context)
    Weight Pb of Return (PR * % of Total) = SUMX(Sheet1,Sheet1[Probability of Return]) * [% of total]

  4. SUMX/summarise Weight Pb of Return values between 2 date period to remove details in between (to have one start and end date)

    Weight Pb of Return Waterfall =

    VAR max_date = [max_date]
    VAR min_date = [min_date]

    Return

    SUMX(FILTER(Sheet1,OR(Sheet1[Sales Date] = max_date, Sheet1[Sales Date] = min_date)), [Weight Pb of Return (PR * % of Total)])

    5. Dynamic date selector values
    max_date = CALCULATE(MAX(Sheet1[Sales Date]), ALLSELECTED(Sheet1))
    min_date = CALCULATE(MIN(Sheet1[Sales Date]), ALLSELECTED(Sheet1))
     
    After applying above calculations I am getting below as result (Start date 30 June and end date 30 Sep - no July or Aug bars) where waterfall is giving incorrect green and red bar values between 2 blue bars and not completing the cycle (line over 5.96%)

     
     

    image.png

     

    Summarised numbers for Region ABC should be (based on data in appendix and follwing process of calculation)

    A = -0.24%
    B = +0.09%
    C = +0.06%

    Summarised visual should come as follow (I did calculations in excels and straight way using values from fields with one SUMX expression on top of excel calculations)

    Weighted Return % Waterfall =

    VAR max_date = [max_date]
    VAR min_date = [min_date]

    Return

    SUMX(FILTER(Solution,OR(Solution[Sales Date] = max_date, Solution[Sales Date] = min_date)), Solution[Weight Pb of Return]
    )


    2.PNG

Let me know if anybody can help me getting summarised waterfall as above.

Thanks in Advance

Appendix

Data used for this report

Monthly sales data with probability of return for each loan product and some meta data information around products. 

PS : Monthly Total, % of Total and Weight Pb of Retrun columns are excel calculations which I want in Power BI.

 

Sales DateCustomer NumberProductRegionSales AmountProbability of ReturnMonthly Total% of TotalWeight Pb of Return
6/30/20201Pro_1A40452840.5%551337097.34%0.04%
6/30/20202Pro_2B38328613.0%551337096.95%0.21%
6/30/20203Pro_3C37716695.5%551337096.84%0.38%
6/30/20204Pro_4A27110067.0%551337094.92%0.34%
6/30/20205Pro_5B40379078.0%551337097.32%0.59%
6/30/20206Pro_6C26937519.0%551337094.89%0.44%
6/30/20207Pro_7A315130911.0%551337095.72%0.63%
6/30/20208Pro_8B372734412.0%551337096.76%0.81%
6/30/20209Pro_9C26271936.0%551337094.77%0.29%
6/30/202010Pro_10A252367014.0%551337094.58%0.64%
6/30/202011Pro_11B37762362.0%551337096.85%0.14%
6/30/202012Pro_12C38251214.0%551337096.94%0.28%
6/30/202013Pro_13A441868810.0%551337098.01%0.80%
6/30/202014Pro_14B37418481.0%551337096.79%0.07%
6/30/202015Pro_15C29706162.5%551337095.39%0.13%
6/30/202016Pro_16A32792064.5%551337095.95%0.27%
7/30/20201Pro_1A21605430.5%322024436.71%0.03%
7/30/20202Pro_2B20056883.0%322024436.23%0.19%
7/30/20203Pro_3C17672995.5%322024435.49%0.30%
7/30/20204Pro_4A15924827.0%322024434.95%0.35%
7/30/20205Pro_5B18423908.0%322024435.72%0.46%
7/30/20206Pro_6C23591689.0%322024437.33%0.66%
7/30/20207Pro_7A158426211.0%322024434.92%0.54%
7/30/20208Pro_8B165612512.0%322024435.14%0.62%
7/30/20209Pro_9C16106576.0%322024435.00%0.30%
7/30/202010Pro_10A232576914.0%322024437.22%1.01%
7/30/202011Pro_11B21400502.0%322024436.65%0.13%
7/30/202012Pro_12C19882174.0%322024436.17%0.25%
7/30/202013Pro_13A222417510.0%322024436.91%0.69%
7/30/202014Pro_14B24411551.0%322024437.58%0.08%
7/30/202015Pro_15C24322352.5%322024437.55%0.19%
7/30/202016Pro_16A20722284.5%322024436.44%0.29%
8/31/20201Pro_1A20274330.5%313190666.47%0.03%
8/31/20202Pro_2B19837523.0%313190666.33%0.19%
8/31/20203Pro_3C19862315.5%313190666.34%0.35%
8/31/20204Pro_4A21722027.0%313190666.94%0.49%
8/31/20205Pro_5B18928538.0%313190666.04%0.48%
8/31/20206Pro_6C18273009.0%313190665.83%0.53%
8/31/20207Pro_7A192155911.0%313190666.14%0.67%
8/31/20208Pro_8B219753912.0%313190667.02%0.84%
8/31/20209Pro_9C17009306.0%313190665.43%0.33%
8/31/202010Pro_10A203235114.0%313190666.49%0.91%
8/31/202011Pro_11B19086332.0%313190666.09%0.12%
8/31/202012Pro_12C17517364.0%313190665.59%0.22%
8/31/202013Pro_13A203789210.0%313190666.51%0.65%
8/31/202014Pro_14B18840401.0%313190666.02%0.06%
8/31/202015Pro_15C20598592.5%313190666.58%0.16%
8/31/202016Pro_16A19347564.5%313190666.18%0.28%
9/30/20201Pro_1A12393150.5%195348856.34%0.03%
9/30/20202Pro_2B13656523.0%195348856.99%0.21%
9/30/20203Pro_3C10493105.5%195348855.37%0.30%
9/30/20204Pro_4A13596627.0%195348856.96%0.49%
9/30/20205Pro_5B13724158.0%195348857.03%0.56%
9/30/20206Pro_6C9610199.0%195348854.92%0.44%
9/30/20207Pro_7A87119711.0%195348854.46%0.49%
9/30/20208Pro_8B149595112.0%195348857.66%0.92%
9/30/20209Pro_9C14729466.0%195348857.54%0.45%
9/30/202010Pro_10A98013114.0%195348855.02%0.70%
9/30/202011Pro_11B13590402.0%195348856.96%0.14%
9/30/202012Pro_12C10931804.0%195348855.60%0.22%
9/30/202013Pro_13A94630710.0%195348854.84%0.48%
9/30/202014Pro_14B14676351.0%195348857.51%0.08%
9/30/202015Pro_15C12649192.5%195348856.48%0.16%
9/30/202016Pro_16A12362064.5%195348856.33%0.28%
1 REPLY 1
technolog
Super User
Super User

The main goal is to create a waterfall visual that shows the weighted probability of return of sales over months, but only for the start and end dates selected in a slicer, without showing the breakdown for the months in between.

The calculations you've provided seem to be on the right track, but there might be some issues with the logic or the way they're being applied. Let's go step by step:

  1. Dynamic month wise sales total: Your formula for monthly_sales seems correct. It calculates the total sales for the selected month(s) based on the slicers and filters applied.

  2. Calculating % of total out of sales amount for each month: The formula % of total is also correct. It divides the sales amount for each row by the monthly total to get the percentage of total sales for that row.

  3. Multiply % of total with Probability of Return: The formula Weight Pb of Return (PR * % of Total) multiplies the probability of return for each row with its % of total. The use of SUMX here is to iterate over each row in the table and perform this calculation.

  4. SUMX/summarise Weight Pb of Return values between 2 date period: This is where things might be getting tricky. The formula Weight Pb of Return Waterfall is supposed to sum up the weighted probabilities of return for only the start and end dates selected in the slicer. The use of FILTER with the OR condition ensures that only rows with either the start or end date are considered. However, if the slicer allows for multi-selection, there might be more than two dates selected, which could mess up the logic. Ensure that the slicer is set to single selection mode.

  5. Dynamic date selector values: The formulas for max_date and min_date are correct. They get the maximum and minimum sales dates from the selected values in the slicer.

From the data you've provided in the appendix, it seems like you have sales data for each product for each region for a few months. If the waterfall is not showing the correct values, there might be a few reasons:

  • Data Model Relationships: Ensure that there are no relationships in the data model that might be affecting the calculations. For example, if there's a relationship between the sales date and another table, it might filter out some rows unintentionally.

  • Slicer Configuration: As mentioned earlier, ensure that the slicer is set to single selection mode. If multiple dates can be selected, the logic for the Weight Pb of Return Waterfall formula might not work as expected.

  • Visual Configuration: Ensure that the waterfall visual is set up correctly. The category should be the sales date, and the values should be the Weight Pb of Return Waterfall measure. Also, check if there are any filters applied to the visual that might be affecting the results.

Lastly, if you've done the calculations in Excel and they're coming out as expected, try to replicate the exact same logic in Power BI. Sometimes, there might be subtle differences in the way calculations are done in Excel vs. Power BI, especially when dealing with complex measures like this.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.