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 - 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 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|
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!