Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Kimcha
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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}})

1.PNG

 

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.

3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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}})

1.PNG

 

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.

3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

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!

Hi @Kimcha,

 

I have uploaded my pbix file for your reference.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Kimcha
Frequent Visitor

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.