Forum Discussion

Kimcha's avatar
Kimcha
Advocate I
9 years ago
Solved

Slicer on summarized values

I have a table that is grouped by "Page" and with sum and counts of pageviews, signups and signups %.

 

 

I would like to filter it to show only rows where pageviews are lower than x or signups higher than x.

 

I am able to do it with the "Visual Level Filters", but I would like to add a control that users can just drag to adjust the values.

 

Is this possible?

 

I have tried to add a slicer, but it only lets me filter the underlying data

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Kimcha,

     

    Perhaps you could try below steps.

     

    Create a calculated table which lists all options you want to allow users to select from a slicer. Later, you should add this column into slicer.

    DataTable = DATATABLE("Selection",Integer,{{10},{300},{500}})

     

    Create measures like below:

    Measure page viewers =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[unique page viewers] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[unique page viewers] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

Measure signups =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[signups] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[signups] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

Measure signups% =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[signups %] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[signups %] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

    Then, add above measures into table visual.

     

    Best regards,
    Yuliana Gu

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Kimcha,

     

    Perhaps you could try below steps.

     

    Create a calculated table which lists all options you want to allow users to select from a slicer. Later, you should add this column into slicer.

    DataTable = DATATABLE("Selection",Integer,{{10},{300},{500}})

     

    Create measures like below:

    Measure page viewers =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[unique page viewers] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[unique page viewers] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

Measure signups =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[signups] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[signups] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

Measure signups% =
IF (
    ISFILTERED ( 'DataTable'[Selection] ),
    CALCULATE (
        SUM ( 'Page table'[signups %] ),
        FILTER (
            'Page table',
            SUM ( 'Page table'[unique page viewers] ) <= MAX ( 'DataTable'[Selection] )
        )
    ),
    CALCULATE (
        SUM ( 'Page table'[signups %] ),
        FILTER (
            'Page table',
            'Page table'[unique page viewers] <= MAX ( 'Page table'[unique page viewers] )
        )
    )
)

    Then, add above measures into table visual.

     

    Best regards,
    Yuliana Gu

    • Kimcha's avatar
      Kimcha
      Advocate I
      9 years ago

      Wow, thank you so much v-yulgu-msft!

       

      I am very new to PowerBi and this is a little over my head. Would it be possibel for you to please upload the file you used to come up with this, so that I can play around with it and figure out how all the pieces come together?

       

      I really appreciate your help!

      • Kimcha's avatar
        Kimcha
        Advocate I
        9 years ago

        I have a table that is grouped by "Page" and with sum and counts of pageviews, signups and signups %.

         

         

        I would like to filter it to show only rows where pageviews are lower than x or signups higher than x.

         

        I am able to do it with the "Visual Level Filters", but I would like to add a control that users can just drag to adjust the values.

         

        Is this possible?

         

        I have tried to add a slicer, but it only lets me filter the underlying data

    • zivhimmel's avatar
      zivhimmel
      Resolver I
      9 years ago

      v-yulgu-msft, I have a question.

       

      In the second part of each measure calculation(the "else" part of isfiltered), isn't it simpler to just use SUM?

      I tried it myself and it works well.

      For example, instead of:

       

      Measure page viewers = IF(ISFILTERED('DataTable'[Selection]),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table',SUM('Page table'[unique page viewers])<=MAX('DataTable'[Selection]))),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table','Page table'[unique page viewers]<=MAX('Page table'[unique page viewers]))))

       

      just use:

       

      Measure page viewers2 = IF(ISFILTERED('DataTable'[Selection]),CALCULATE(SUM('Page table'[unique page viewers]),FILTER('Page table',SUM('Page table'[unique page viewers])<=MAX('DataTable'[Selection]))),SUM('Page table'[unique page viewers]))

       

      Thanks!

       

    • navin1491's avatar
      navin1491
      Frequent Visitor
      7 years ago
      v-yulgu-msft Kimcha This is amazing. This is what I've been unsuccessfully trying to do for the last few weeks. I have one more question here. Would the current slicer work in conjunction with a date slicer. Thanks!