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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.