cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IMPBI Frequent Visitor
Frequent Visitor

Merge/Overlay two line charts

Hello,

 

 

Power BI 2 merge line charts.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We want to show into a single line chart a combined data from two line charts with different filters/interactions in each of them.
This line charts represent the average per ticket/receipt per day.
The bottom left is a line chart per seller.
The bottom right us a line chart with the store average per day.
The selected field is used to filter the sellers and filter the data from the chart on the bottom left.
This slicer is blocked in the line chart from the bottom right.
The data source and table is the same for both.
Can we do something to display this combined data in one line chart?

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Merge/Overlay two line charts

Hi @IMPBI,

 

I don't have any information regarding you setup so I made a table with 3 columns:

  • Date
  • Values
  • Vendor

Also created a date table to create the PY calculation.

 

Based on this setup I have made 3 measures:

Average Current Year = AVERAGE(Fact_table[Value])

Average Pryor Year = CALCULATE(AVERAGE(Fact_table[Value]); DATEADD('Calendar'[Date]; -1 ;YEAR))

Average no Sellers = CALCULATE([Average Current Year];ALL(Fact_table[Vendor]))

Be aware that this 3 measures may not correspond to what you have in your model, again without any addtional information is difficult to help you better, The first and third measure if no seller is select gives the exact same result so in the image below you will see the value overlaped.

 

Then just add this measures to your charts, look at the red line it doesn't move although I'm changing the slicer for vendor.

 

Averages.gif

 

To have the result you need to the calculations you are asking you just need to use the ALL as a filter in your measure.

 

Regards,

MFelix

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




11 REPLIES 11
Super User
Super User

Re: Merge/Overlay two line charts

Hi @IMPBI,

 

Without any data it's difficult to give an answer but based on your assumptions if you create different measure for each of the lines in your line chart it's possible to make it all into one I',m assuming that the X-axis are the same in  both charts.

 

Can you post a sample of the data and a expected result (image is very low resolution) so that I can try and make the measures for you to add.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Anonymous
Not applicable

Re: Merge/Overlay two line charts

Hi @IMPBI,

 

Do you wish to show one line chart at a time based on what is selected?

IMPBI Frequent Visitor
Frequent Visitor

Re: Merge/Overlay two line charts

Hello @MFelix,

 

The image is really terrible (I'll upload a new version). I will try to explain in more detail our situation and our requirements.
The X axis should be same in both line charts (now we are making test with de week number too). We are working with the day number of a calendar table. And the measure is the same too. It is the average amount in euros per ticket/day.
In the bottom left there are this measure compared vs same measure of the same period of last year. All this information could be filtered per seller (slicer in top/left).

 

The second one has the same info but with a different tratment: we do not want it to be affected by the seller filter (this way it will show the store's average and serve as a basis for comparison). With a separated licne chart we can do dat. We are blocking this filtering editing the interactions between elements.

 

Our goal is merge this charts into a new one with three lines (or knowing how to do that if it is possible). One for the current average amounts, year -1 and current average without seller filter applyied.
I hope I've explained our needs better.

 

Thank you very much.

IMPBI Frequent Visitor
Frequent Visitor

Re: Merge/Overlay two line charts

Hello, everybody,

Let me return to this subject from another point of view and see if it is more accurate: we need three measures, one of which is not affected by the VENDORS/SELLERS slicer filter at the top.
Could the ALLEXECEPT function works in the new measure?
Would it be better to create a new table without that field? If so, better in the modeling phase or in Power Query?

 

Many thanks. We are stuck at this point...

Super User
Super User

Re: Merge/Overlay two line charts

Can you share some sample data?

Regards, MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Merge/Overlay two line charts

Hi @IMPBI,

 

I don't have any information regarding you setup so I made a table with 3 columns:

  • Date
  • Values
  • Vendor

Also created a date table to create the PY calculation.

 

Based on this setup I have made 3 measures:

Average Current Year = AVERAGE(Fact_table[Value])

Average Pryor Year = CALCULATE(AVERAGE(Fact_table[Value]); DATEADD('Calendar'[Date]; -1 ;YEAR))

Average no Sellers = CALCULATE([Average Current Year];ALL(Fact_table[Vendor]))

Be aware that this 3 measures may not correspond to what you have in your model, again without any addtional information is difficult to help you better, The first and third measure if no seller is select gives the exact same result so in the image below you will see the value overlaped.

 

Then just add this measures to your charts, look at the red line it doesn't move although I'm changing the slicer for vendor.

 

Averages.gif

 

To have the result you need to the calculations you are asking you just need to use the ALL as a filter in your measure.

 

Regards,

MFelix

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




IMPBI Frequent Visitor
Frequent Visitor

Re: Merge/Overlay two line charts

Hello @MFelix,

 

Your solution has been of great help to us and has allowed us to understand better the concept of context filters.
In our model we have the an internal code for each seller and a related table with the attributes of these vendors. We have use the field NAMESELLER (same field used in the slicer) in the ALL function and now it's working (previously we had use SELLERID of the fact table and the results weren't as expected).
We are very grateful for your help.

Many thanks.

aznariy Regular Visitor
Regular Visitor

Re: Merge/Overlay two line charts

what did you use as Axis in this solution?

Super User
Super User

Re: Merge/Overlay two line charts

Hi @aznariy,

 

The axis is the month on the calendar table.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!