The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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)
Summarised numbers for Region ABC should be (based on data in appendix and follwing process of calculation)
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)
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 Date | Customer Number | Product | Region | Sales Amount | Probability of Return | Monthly Total | % of Total | Weight Pb of Return |
6/30/2020 | 1 | Pro_1 | A | 4045284 | 0.5% | 55133709 | 7.34% | 0.04% |
6/30/2020 | 2 | Pro_2 | B | 3832861 | 3.0% | 55133709 | 6.95% | 0.21% |
6/30/2020 | 3 | Pro_3 | C | 3771669 | 5.5% | 55133709 | 6.84% | 0.38% |
6/30/2020 | 4 | Pro_4 | A | 2711006 | 7.0% | 55133709 | 4.92% | 0.34% |
6/30/2020 | 5 | Pro_5 | B | 4037907 | 8.0% | 55133709 | 7.32% | 0.59% |
6/30/2020 | 6 | Pro_6 | C | 2693751 | 9.0% | 55133709 | 4.89% | 0.44% |
6/30/2020 | 7 | Pro_7 | A | 3151309 | 11.0% | 55133709 | 5.72% | 0.63% |
6/30/2020 | 8 | Pro_8 | B | 3727344 | 12.0% | 55133709 | 6.76% | 0.81% |
6/30/2020 | 9 | Pro_9 | C | 2627193 | 6.0% | 55133709 | 4.77% | 0.29% |
6/30/2020 | 10 | Pro_10 | A | 2523670 | 14.0% | 55133709 | 4.58% | 0.64% |
6/30/2020 | 11 | Pro_11 | B | 3776236 | 2.0% | 55133709 | 6.85% | 0.14% |
6/30/2020 | 12 | Pro_12 | C | 3825121 | 4.0% | 55133709 | 6.94% | 0.28% |
6/30/2020 | 13 | Pro_13 | A | 4418688 | 10.0% | 55133709 | 8.01% | 0.80% |
6/30/2020 | 14 | Pro_14 | B | 3741848 | 1.0% | 55133709 | 6.79% | 0.07% |
6/30/2020 | 15 | Pro_15 | C | 2970616 | 2.5% | 55133709 | 5.39% | 0.13% |
6/30/2020 | 16 | Pro_16 | A | 3279206 | 4.5% | 55133709 | 5.95% | 0.27% |
7/30/2020 | 1 | Pro_1 | A | 2160543 | 0.5% | 32202443 | 6.71% | 0.03% |
7/30/2020 | 2 | Pro_2 | B | 2005688 | 3.0% | 32202443 | 6.23% | 0.19% |
7/30/2020 | 3 | Pro_3 | C | 1767299 | 5.5% | 32202443 | 5.49% | 0.30% |
7/30/2020 | 4 | Pro_4 | A | 1592482 | 7.0% | 32202443 | 4.95% | 0.35% |
7/30/2020 | 5 | Pro_5 | B | 1842390 | 8.0% | 32202443 | 5.72% | 0.46% |
7/30/2020 | 6 | Pro_6 | C | 2359168 | 9.0% | 32202443 | 7.33% | 0.66% |
7/30/2020 | 7 | Pro_7 | A | 1584262 | 11.0% | 32202443 | 4.92% | 0.54% |
7/30/2020 | 8 | Pro_8 | B | 1656125 | 12.0% | 32202443 | 5.14% | 0.62% |
7/30/2020 | 9 | Pro_9 | C | 1610657 | 6.0% | 32202443 | 5.00% | 0.30% |
7/30/2020 | 10 | Pro_10 | A | 2325769 | 14.0% | 32202443 | 7.22% | 1.01% |
7/30/2020 | 11 | Pro_11 | B | 2140050 | 2.0% | 32202443 | 6.65% | 0.13% |
7/30/2020 | 12 | Pro_12 | C | 1988217 | 4.0% | 32202443 | 6.17% | 0.25% |
7/30/2020 | 13 | Pro_13 | A | 2224175 | 10.0% | 32202443 | 6.91% | 0.69% |
7/30/2020 | 14 | Pro_14 | B | 2441155 | 1.0% | 32202443 | 7.58% | 0.08% |
7/30/2020 | 15 | Pro_15 | C | 2432235 | 2.5% | 32202443 | 7.55% | 0.19% |
7/30/2020 | 16 | Pro_16 | A | 2072228 | 4.5% | 32202443 | 6.44% | 0.29% |
8/31/2020 | 1 | Pro_1 | A | 2027433 | 0.5% | 31319066 | 6.47% | 0.03% |
8/31/2020 | 2 | Pro_2 | B | 1983752 | 3.0% | 31319066 | 6.33% | 0.19% |
8/31/2020 | 3 | Pro_3 | C | 1986231 | 5.5% | 31319066 | 6.34% | 0.35% |
8/31/2020 | 4 | Pro_4 | A | 2172202 | 7.0% | 31319066 | 6.94% | 0.49% |
8/31/2020 | 5 | Pro_5 | B | 1892853 | 8.0% | 31319066 | 6.04% | 0.48% |
8/31/2020 | 6 | Pro_6 | C | 1827300 | 9.0% | 31319066 | 5.83% | 0.53% |
8/31/2020 | 7 | Pro_7 | A | 1921559 | 11.0% | 31319066 | 6.14% | 0.67% |
8/31/2020 | 8 | Pro_8 | B | 2197539 | 12.0% | 31319066 | 7.02% | 0.84% |
8/31/2020 | 9 | Pro_9 | C | 1700930 | 6.0% | 31319066 | 5.43% | 0.33% |
8/31/2020 | 10 | Pro_10 | A | 2032351 | 14.0% | 31319066 | 6.49% | 0.91% |
8/31/2020 | 11 | Pro_11 | B | 1908633 | 2.0% | 31319066 | 6.09% | 0.12% |
8/31/2020 | 12 | Pro_12 | C | 1751736 | 4.0% | 31319066 | 5.59% | 0.22% |
8/31/2020 | 13 | Pro_13 | A | 2037892 | 10.0% | 31319066 | 6.51% | 0.65% |
8/31/2020 | 14 | Pro_14 | B | 1884040 | 1.0% | 31319066 | 6.02% | 0.06% |
8/31/2020 | 15 | Pro_15 | C | 2059859 | 2.5% | 31319066 | 6.58% | 0.16% |
8/31/2020 | 16 | Pro_16 | A | 1934756 | 4.5% | 31319066 | 6.18% | 0.28% |
9/30/2020 | 1 | Pro_1 | A | 1239315 | 0.5% | 19534885 | 6.34% | 0.03% |
9/30/2020 | 2 | Pro_2 | B | 1365652 | 3.0% | 19534885 | 6.99% | 0.21% |
9/30/2020 | 3 | Pro_3 | C | 1049310 | 5.5% | 19534885 | 5.37% | 0.30% |
9/30/2020 | 4 | Pro_4 | A | 1359662 | 7.0% | 19534885 | 6.96% | 0.49% |
9/30/2020 | 5 | Pro_5 | B | 1372415 | 8.0% | 19534885 | 7.03% | 0.56% |
9/30/2020 | 6 | Pro_6 | C | 961019 | 9.0% | 19534885 | 4.92% | 0.44% |
9/30/2020 | 7 | Pro_7 | A | 871197 | 11.0% | 19534885 | 4.46% | 0.49% |
9/30/2020 | 8 | Pro_8 | B | 1495951 | 12.0% | 19534885 | 7.66% | 0.92% |
9/30/2020 | 9 | Pro_9 | C | 1472946 | 6.0% | 19534885 | 7.54% | 0.45% |
9/30/2020 | 10 | Pro_10 | A | 980131 | 14.0% | 19534885 | 5.02% | 0.70% |
9/30/2020 | 11 | Pro_11 | B | 1359040 | 2.0% | 19534885 | 6.96% | 0.14% |
9/30/2020 | 12 | Pro_12 | C | 1093180 | 4.0% | 19534885 | 5.60% | 0.22% |
9/30/2020 | 13 | Pro_13 | A | 946307 | 10.0% | 19534885 | 4.84% | 0.48% |
9/30/2020 | 14 | Pro_14 | B | 1467635 | 1.0% | 19534885 | 7.51% | 0.08% |
9/30/2020 | 15 | Pro_15 | C | 1264919 | 2.5% | 19534885 | 6.48% | 0.16% |
9/30/2020 | 16 | Pro_16 | A | 1236206 | 4.5% | 19534885 | 6.33% | 0.28% |
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:
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.
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.
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.
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.
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.
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |