Waterfall SUMX

[sgirdha3]

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

   

   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]

   )

   
   
   

   

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%

[technolog]

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.