Merge/Overlay two line charts

[IMPBI]

Hello,

 

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.

[Anonymous]

Hi @IMPBI,

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

[MFelix]

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

[IMPBI]

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]

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...

[MFelix · Accepted solution]

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.

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

[aznariy]

what did you use as Axis in this solution?

[MFelix]

Hi @aznariy,

The axis is the month on the calendar table.

Regards,

MFelix

[aznariy]

then you may be able to help me with the problem that I'm facing 

https://community.powerbi.com/t5/Issues/Unable-to-draw-multiple-lines-on-line-chart-correctly/idi-p/...

appreciate your help!

[MFelix]

Hi @aznariy,

Just posted the answer on the post,

See attach the PBIX file I created.

Regards,

MFelix

[IMPBI]

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.

[MFelix]

Can you share some sample data?

Regards, MFelix