Forum Discussion

Kimcha's avatar
Kimcha
Icon for Advocate I rankAdvocate 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 signup...
  • 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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft 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

zivhimmel's avatar
zivhimmel
Icon for Resolver I rankResolver 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!