cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freybellet
Regular Visitor

Merge/summing revenue from different datasets in one chart

Hi,

 

I have daily revenue from different businesses (each in a different table/dataset, imported in Power BI via a SQL query or based on Excel files) and I am trying to build a master chart showing the sum of the businesses selected by the end user. If I select two businesses making 100$ per day, don't want two lines at 100$, but one at 200$.Master slide example.PNG

 

  1. A stacked area chart could do the job, if it is possible to create a slicer on the side like on the example [screenshot] below. Do you know a smart way to create a slicer that filters in or out a business when the businesses come from different datasets/tables?
  2. Additionnally, I would need to show the revenue budgeted for the selected Like badly drawn on my screenshot, 1 line on the chart that moves up and down depending on the businesses the end user includes in the chart. 

If I can see some workarounds for my issue 1) above, I am totally stuck with the second one. 

 

Thanks a lot in advance for your awesome ideas, 

 

Fred

2 REPLIES 2
OscLar
Helper I
Helper I

Maybe I'm missing your point but you could try to build a single table in power query when importing fom the different excel filse. Then when you have all the data in one table its easier to create a slicer.

 

Or when you have all the different tables in PQ, append all to one new table. Though my very unqualified quess is that the first way is probably better if you have lots of data. But don't quote me on it...

 

Cheers,

 

OscLar

Thanks for your reply OscLar. Actually the tables are all very different, only Daily Revenue is a common metric, and come from very various sources (different Data Warehouses, Excel files,...). And we are talking about millions of rows. But yes, I agree, that's one of the workarounds I am considering. 

 

My main problem though is the second one. Can I create a measure that sums up whatever is selected in the slicer, like the Budget line in my screenshot...?

If budget for line 1 is 100$, budget for line 2 is 50$ and budget for line 3 is 1$, by selecting 1 and 3 I would have a budget line at 103$ in addition to the stacked area with the revenue actuals.

 

Thank you so much

 

Fred 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.