locked
Need DAX query that mimics the Functionality of the new ALLSELECTED Function RRS feed

  • Question

  • I need a way to get the % of a number of the total filtered values. The ALL function looks at the entire underlying table, not the filtered data set. In the new version of PowerPivot in Beta there is the ALLSELECTED function that returns the column total of the filtered data set. How can I do this in V1 of PowerPivot?
    Wednesday, February 29, 2012 10:22 PM

Answers

  • It is not completely clear to me what your entire dataset or data model even looks like. However, from what I can see, the technique mentioned can indeed help.  When you do a % of Total, you normally think of the ALL function in the denominator. However, ALL can clear the context on the column or table, and if you have a slicer selection on that column it will ignore it.   By creating a copy of the column (as a new calculated column), you can keep the context on that second column and generate a % of total 'selected'.  This what is normally referred to as 'Visual Totals'.

    Can you send me the file? In that way I can more directly related to your data model.   javiguillen@hotmail.com




    Javier Guillen
    http://javierguillen.wordpress.com/

    Friday, March 2, 2012 1:57 AM
    Answerer

All replies

  • Whalensdad,

    In PowerPivot v1, you can use the technique described on this blog entry:

    http://javierguillen.wordpress.com/2011/09/13/using-slicer-values-in-dax-calculations/




    Javier Guillen
    http://javierguillen.wordpress.com/

    Thursday, March 1, 2012 2:44 AM
    Answerer
  • Unfortuanely that method won't work, or I can't make it work. Here is why:

    The pivot table that would be using this measure does not have a column. It is made up of a row and the measure result. The pivot is already tied to a slicer, but I can't tie the slicers selections directly to the underlying table. Here is the current query behind the slicer measure:

    Slicer_Measure=IF(COUNTROWS(Date_Period_Filter)=1,

    SUMX(Filter(Raw_Data,Raw_Data[Filter_Date]>=SUM(Date_Period_Filter[CUTOFF_DATE])),

    Raw_Data[RecordCount]),BLANK())

    Here is a snapshot of the data elements. Using the pivot in Excel is not an issue because I can set the Value Field Setting to be % of Column, but I want to use the underlying PowerPivot data in PerformancePoint so I need to have a measure that will give me the % values.

    

    Thursday, March 1, 2012 2:40 PM
  • It is not completely clear to me what your entire dataset or data model even looks like. However, from what I can see, the technique mentioned can indeed help.  When you do a % of Total, you normally think of the ALL function in the denominator. However, ALL can clear the context on the column or table, and if you have a slicer selection on that column it will ignore it.   By creating a copy of the column (as a new calculated column), you can keep the context on that second column and generate a % of total 'selected'.  This what is normally referred to as 'Visual Totals'.

    Can you send me the file? In that way I can more directly related to your data model.   javiguillen@hotmail.com




    Javier Guillen
    http://javierguillen.wordpress.com/

    Friday, March 2, 2012 1:57 AM
    Answerer