cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tjhoolahan
Helper I
Helper I

Waterfall chart query - show absolute numbers

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:

 

tjhoolahan_0-1594201307910.png

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.

 

tjhoolahan_1-1594201549179.png

 

5 REPLIES 5
amitchandak
Super User IV
Super User IV

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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?

@tjhoolahan , share some sample data in table format



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak hope this works for you?

 

Status          Date                       Client Numbers

Total01 January 202010000
New01 January 202050
Lost01 January 2020-75
Recovered01 January 202015
Total01 February 20209990
New01 February 202053
Lost01 February 2020-40
Recovered01 February 202021
Total01 March 202010024
New01 March 202060
Lost01 March 2020-80
Recovered01 March 202010
Total01 April 202010014

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

Best,

Tim

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors