none
Dynamic Measure - Selecting which fields to include in a calculation

    Question

  • Hello all,

    and thank you so much for spending your time reading this. I'm working on PowerPivot in Excel 2010. But if you know there's a solution in 2013, please let me know and I'll try to justify an upgrading. I have a table for my data source where the rows are products and I have a list of columns with years. Each cell is the number of items sold:

    I'm setting up a spreadsheet in Excel 2010 and also a page in PowerView to compare how they look. I have some slicers that work on which items to be included in the calculations, ie. average sales. Is there a way to create a slicer / filter fo the user to select which years to include in the calculation of the average! For example: With slicers to select item 1 and item 2, but only years 2012 and 2013 to be included in the calculation of an average of sales? I can easily create a slicer on items and a measure to calculate that average. However, I can't put the years in the equation.

    Is there a way? Thank you

    • Edited by Nikitas10 Thursday, January 23, 2014 4:22 PM
    Thursday, January 23, 2014 4:21 PM

Answers

  • You could accomplish this with the technique of using disconnected tables/slicers in PowerPivot.  There are quite a few blog posts out there about different applications of disconnected tables/slicers.  Basically, it allows for using slicers as a means of entering parameters into your measures.  Although extremely useful in many situations, it may be a little advanced and confusing for a new Power Pivot user. 

    An easier option may be to reshape (unpivot) your data.  Instead of a short, wide table, it could be transformed into a long skinny table.  In other words, instead of having a different column for each year, you would only have a single column called Year and another column called Values.  The Items column would simply contain duplicate entries for each item depending on the number of years.  No matter how many years are added in the future, you data table would still only have 3 columns (Items, Year, and Value). In this scenario, measures are usually easier to write and the year column could also be used as a slicer.  The bonus is that PowerPivot is designed to perform much faster on long, skinny tables with fewer columns.

    If your source data is from a database such as SQL Server, then your DBA can most likely create an unpivoted view for you pretty easily.  If that isn't an option or your data is from another source, you should download Power Query.  This is free Excel Add-in from Microsoft, in the same family as Power Pivot, that is designed to reshape data from many different sources with no coding necessary in most cases.  Power Query has an Unpivot command built in and the results can be imported directly to Power Pivot.

    • Marked as answer by Nikitas10 Friday, January 24, 2014 10:01 AM
    Thursday, January 23, 2014 5:45 PM
    Answerer

All replies

  • You could accomplish this with the technique of using disconnected tables/slicers in PowerPivot.  There are quite a few blog posts out there about different applications of disconnected tables/slicers.  Basically, it allows for using slicers as a means of entering parameters into your measures.  Although extremely useful in many situations, it may be a little advanced and confusing for a new Power Pivot user. 

    An easier option may be to reshape (unpivot) your data.  Instead of a short, wide table, it could be transformed into a long skinny table.  In other words, instead of having a different column for each year, you would only have a single column called Year and another column called Values.  The Items column would simply contain duplicate entries for each item depending on the number of years.  No matter how many years are added in the future, you data table would still only have 3 columns (Items, Year, and Value). In this scenario, measures are usually easier to write and the year column could also be used as a slicer.  The bonus is that PowerPivot is designed to perform much faster on long, skinny tables with fewer columns.

    If your source data is from a database such as SQL Server, then your DBA can most likely create an unpivoted view for you pretty easily.  If that isn't an option or your data is from another source, you should download Power Query.  This is free Excel Add-in from Microsoft, in the same family as Power Pivot, that is designed to reshape data from many different sources with no coding necessary in most cases.  Power Query has an Unpivot command built in and the results can be imported directly to Power Pivot.

    • Marked as answer by Nikitas10 Friday, January 24, 2014 10:01 AM
    Thursday, January 23, 2014 5:45 PM
    Answerer
  • Hello Mike,

    thank you so much for that information. The example I used with the sales items is of course quite simplified compared to the data table I have (24906 rows and 32 columns). We have no DBA and unpivoting that table would be quite a task.

    The first option you suggested sounds great and I plan on investigating the disconnected tables/slicers and see if that will work. I'm looking forward to seeing how they work. I like learning new things about this technology anyway.

    Friday, January 24, 2014 10:07 AM
  • Glad it helped but just so you know, unpivoting the table in Power Query would be a fairly simple task that you could accomplish yourself with a few button clicks. Whatever route you choose...good luck!
    Friday, January 24, 2014 12:37 PM
    Answerer