Forum Discussion
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.
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
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.
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
Super User
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
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.
what did you use as Axis in this solution?
- MFelix
Hi Anonymous,
The axis is the month on the calendar table.
Regards,
MFelix
- MFelixCan you share some sample data?
Regards, MFelix