Waterfall chart query - show absolute numbers

[tjhoolahan]

Hi,

I am looking to build a waterfall chart in Power BI that shows the total number of clients each month, with a breakdown of the specific number of new, recovered and lost clients that occurred between each month. This was achieved in Excel, as the image below shows, with some sample data for each category:

When I do the same thing in Power BI though, I can't get the absolute values of new, lost and recovered clients to show but the change in their values month to month. E.g. if the number of lost clients was -75 in Janary 2020 and -40 in February 2020, the value of the breakdown for lost clients would be +35, which represents the change in value between the two months (as the image below shows). How do I fix this so that only the absolute value for the month in question is shown, as per the Excel chart, rather than the month to month change? Am I missing something obvious or is using the Status (New, Lost or Recovered) as the breakdown not correct? Many thanks in advance and please let me know if anything isn't clear. As stated above, I've only used sample data to try and get what I want, so there's no data set to share here.

[amitchandak]

@tjhoolahan , I doubt I got. See if this can help

https://www.youtube.com/watch?v=7DW5wD2j1xw

[tjhoolahan]

@amitchandak - unfortunately, this video does not help. I've already applied those steps and because it is summing the client numbers for each date and comparing them to the next date, you don't see any of the individual decrease, caused by losing clients. This is where the "breakdown" should come into play but, as I mentioned before, when you add the client status, which shows whether it is lost, new or recovered, it compares the month to month change in the values for that status, which is not what is displayed in the Excel (i.e. when comparing Jan to Feb, it looks at the difference between the number of new clients in each month, rather than displaying the number of new clients in Jan and how that contributes to the Feb client total). Any ideas on this? Maybe you could quickly replicate the table I've shown in the image in Excel and play around with it in Power BI to try and get the exact same waterfall chart as in Excel?

[amitchandak]

@tjhoolahan , share some sample data in table format

[tjhoolahan]

@amitchandak hope this works for you?

Status          Date                       Client Numbers

Total	01 January 2020	10000
New	01 January 2020	50
Lost	01 January 2020	-75
Recovered	01 January 2020	15
Total	01 February 2020	9990
New	01 February 2020	53
Lost	01 February 2020	-40
Recovered	01 February 2020	21
Total	01 March 2020	10024
New	01 March 2020	60
Lost	01 March 2020	-80
Recovered	01 March 2020	10
Total	01 April 2020	10014

[tjhoolahan]

@amitchandak, did you have a chance to test with the sample data I provided?

Best,

Tim