locked
Possible to have all slicer options on the same chart series as individual slicer options? RRS feed

  • Question

  • Hi Everyone

    I don't think this is logically possible, but if I have a slicer for a Powerpivot chart that is in the Legend/Series of that chart, is it possible to have both individual values of that slicer appear on the chart as well as a series that represents all the values of the slicer?

    So for example, in the below chart, there could be one series each for 2006 and 2007 on the chart and a third series for all years combined at the same time? I would assume this would involve another slicer, but don't know if it is possible.

    Paul


    Monday, April 28, 2014 6:01 PM

Answers

  • Hi Paul,

    Are you using the new field Judge/Referee from your new Judge-Dimension table as the legend/series? Using the existing field from your s1Perm1-table could result in the behavior that you described. Have you changed your chart to use the new measure?

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    • Marked as answer by Paul-NYS Friday, May 2, 2014 1:56 PM
    Friday, May 2, 2014 8:06 AM

All replies

  • Hi Paul,

    If I understand you correctly you actually want to see the grand total on your chart as a separate line, right? Unfortunately Pivot Charts do not provide that out of the box. One option would be to manually create a "normal" chart from it as described here but I have not tried that yet.

    Another (in my opinion) nice option to solve this is to create an "artificial" all-element in one of your slicers. This could for example be accomplished by using a date table (instead of your month/year-fields from the fact table) that is related to the date-field from your fact-table. You would then add another element to that date-table such as the last line from this linked table:

    Year
    2008
    2009
    2010
    2011
    2012
    2013
    All (Average)
      

    Using this I created a new measure based on your existing CumPercent:

    =if(HASONEVALUE('Date'[Year]),
      if(values('Date'[Year])="All (Average)",
        calculate([CumPercent],all('Date'[Year])),[CumPercent]),[CumPercent])

    If you replace your existing measure with this new one and your StartYear with the new Year-Column from our date-table, an additional all-element will Ssow up on your Pivot Chart:

    Would that help?

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Monday, April 28, 2014 8:24 PM
  • Hi Julian
    I think that will work. I cannot access my files right now, but will tomorrow morning. Two quick items though, if you are around, where are you creating that Date table? I create my tables in Access and then bring them into Powerpivot and then into Excel.

    Also, is there a calculation behind the "All (average)" value or is "all (average)" simply a text value in the Date table?

    Paul
    Monday, April 28, 2014 9:30 PM
  • Hi Paul,

    It depends where I normally create date tables. I try to avoid linked tables and prefer to create them as early in the data flow as possible (when using SQL-Server e.g. I would use SQL-Views). In your case it could make sense to create it in Access as described here: http://www.wiseowl.co.uk/blog/s337/powerpivot-calendar.htm

    All (average) is just a "random" text value in my date table without any calculation behind. All the "magic" happens in that calculated measure.

    Regards,

    Julian 


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Tuesday, April 29, 2014 6:29 AM
  • Hi Julian

    I implemented the above approach on a judge name/ID column that is not in the model I sent you because it is confidential. I created a separate judge dimension table with judge ID, name and title columns and added an option for "1 - All Judges" as both an ID value and a name value. I linked this new dimension table to the fact table in Powerpivot using the judge ID field. And I displayed the judge name field from the judge dimension table on the spreadsheet because users would obviously not use an ID field.

    Everything works fine except one item: when I select the '1 - All Judges" option from the Judge/Referee slicer, all the judge values are displayed individually and not one aggregated '1 - All Judges' value. Your screenshot above displays it correctly.

    There are no errors and everything appears OK (except this)?

    Paul

    =if(HASONEVALUE('s1JudgeIds'[JudgeName]),
      if(values('s1JudgeIds'[JudgeName])="1 - All Judges",
        calculate([CumPercent],all('s1JudgeIds'[JudgeName])),[CumPercent]),[CumPercent])

    Wednesday, April 30, 2014 6:15 PM
  • Hi Paul,

    Are you using the new field Judge/Referee from your new Judge-Dimension table as the legend/series? Using the existing field from your s1Perm1-table could result in the behavior that you described. Have you changed your chart to use the new measure?

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    • Marked as answer by Paul-NYS Friday, May 2, 2014 1:56 PM
    Friday, May 2, 2014 8:06 AM
  • Hi Julian

    Using the Judge/Referee column from the fact table and not from the Judge Dimension table was the issue. Thanks a lot!

    One question on the formula, why is the reason for checking for the presence of one value in the opening statement?

    =if(HASONEVALUE('Date'[Year]),
      if(values('Date'[Year])="All (Average)",
        calculate([CumPercent],all('Date'[Year])),[CumPercent]),[CumPercent])

    Paul

    Friday, May 2, 2014 1:56 PM
  • Hi Paul,

    HASONEVALUE "protects" the values-function in the second line from throwing an error if multiple values would be processed what for example could happen if no slicer value ischosen.

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Saturday, May 3, 2014 6:07 PM
  • OK that makes sense and thanks a lot Julian. And Hasonevalue does not seem to have an issue with multiple selections from a slicer.

    Paul

    Monday, May 5, 2014 7:10 PM