cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
freybellet Frequent Visitor
Frequent 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 Regular Visitor
Regular Visitor

Re: Merge/summing revenue from different datasets in one chart

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

freybellet Frequent Visitor
Frequent Visitor

Re: Merge/summing revenue from different datasets in one chart

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