Slicer on summarized values

[Kimcha]

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 · Accepted solution]

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

[navin1491]

@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!

[zivhimmel]

@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!

[Kimcha]

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!

[v-yulgu-msft]

Hi @Kimcha,

I have uploaded my pbix file for your reference.

Regards,
Yuliana Gu

[Kimcha]

Awesome, this is working well! Thank you very much!

[Kimcha]

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