Waterfall SUMX

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.