Forum Discussion

IMPBI's avatar
IMPBI
Frequent Visitor
8 years ago
Solved

Merge/Overlay two line charts

Hello,                                           We want to show into a single line chart a combined data from two line charts with different filters/interactions in each of...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

     

     

     

MFelix's avatar
MFelix
Super User
8 years ago

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

 

 

 

IMPBI's avatar
IMPBI
Frequent Visitor
8 years ago

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.