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, 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...
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?
Hi Anonymous,
The axis is the month on the calendar table.
Regards,
MFelix
then you may be able to help me with the problem that I'm facing
appreciate your help!