cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
featleyd Regular Visitor
Regular Visitor

Creating a measure which incorporates filters (slicers)

Hello

 

I have a dataset which looks at web pageviews.

 

Each page has a month, year, device used and the number of pageviews

 

eg)

Page      Device     Month    Year     Pageviews

/home   Mobile     03           2019    20467

/help     Desktop   03           2019    157

/help     Mobile     03           2019    206

/help     Tablet      03           2019    100

 

I would like to create a measure (so I can then use it in further calculations) which takes the number of pageviews for a particular page.

 

So for example, if I was reporting on the /help page, and using the table above, assuming my slicers are set to month 03 and year 2019, i would like a measure which brings back 463 (the total of all /help pageviews, but ignoring the pageviews for /home)

 

The reason I am doing this is because I need to display the device split (shown above) as a percentage, and this needs to be shown in a card, so one card showing 'Desktop 33.9%' (157/463*100) , another showing 'Mobile 44.5%' (206/463*100) and 'Tablet 21.6%' (100/463*100) (if my Maths is correct) I will create separate measures for these.

 

I'm aware I can easily achieve this using %GT in a table visual, but my stakeholders would like the card visual.

 

Any help you could give me would be greatly appreciated!

 

Daniel

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating a measure which incorporates filters (slicers)

Hi @featleyd 

 

I agree with @d_gosbell , attached pbix might helpful to you.

004.PNG

Community Support Team _ Dina Ye
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

featleyd Regular Visitor
Regular Visitor

Re: Creating a measure which incorporates filters (slicers)

Thanks again for your help everyone.

 

I've managed to solve it using the following:

 

My pageviews measure was simply 

SUM('Table'[Pageviews])
 
Once I had that, I found it easier to calculate the remaining cards via their own measures, although I completely agree with your suggested method, and that would benefit other users.
 
Thanks again for your help everybody.

View solution in original post

9 REPLIES 9
Highlighted
Super User
Super User

Re: Creating a measure which incorporates filters (slicers)

By default measures will calculate within the context provided by slicers. So if you filter for month = 03, year = 2019 and page = /help you should get 463. But if you have cards with an extra filter applied on the device you would want to ignore the device for your denominator. You should be able to do this with the ALL() function inside a calculate function.

 

eg.

 

Device % = SUM(  table[pageviews] )  /  CALCULATE( SUM( table[pageviews] ),  ALL( table[Device] ) )

Community Support Team
Community Support Team

Re: Creating a measure which incorporates filters (slicers)

Hi @featleyd ,

 

Let me know if you'd like to get below results:

Measure = CALCULATE(SUM('Table'[Pageviews]),ALLEXCEPT('Table','Table'[Page]))
Measure 2 = CALCULATE(SUM('Table'[Pageviews]),ALLEXCEPT('Table','Table'[Device],'Table'[Page]))/[Measure]

09.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
featleyd Regular Visitor
Regular Visitor

Re: Creating a measure which incorporates filters (slicers)

Hello both

 

Thanks for your assistance on this.

 

I require the measure to ignore the pageviews for /home and only bring back pageviews for /help.

 

To clarify my slicers will be set to month and year, but I only want the visuals on the page to refer to /help, ignoring the /home pageviews.

 

I aso need to put each individual device's % of the grand total into it's own card visual, so I'd essentially need 4 measures:

 

1) The first to ascertain the total pageviews for /help (463) in the example.

2) The second the get the percentage of 463 attributed to 'Tablet'

3) The third to get the percentage of 463 attributed to 'Mobile'

4) The third to get the percentage of 463 attributed to 'Desktop'

 

Let me know if I'm missing something obvious!

 

I can't seem to achieve this in the above solutions? (Although I'm probably missing something obvious!

Super User
Super User

Re: Creating a measure which incorporates filters (slicers)


@featleyd wrote:

I aso need to put each individual device's % of the grand total into it's own card visual, so I'd essentially need 4 measures:

 


I don't think you need 4 separate measures. Just add 4 card visuals and then add a visual filter to each card for page="/help" (or you could set this as a slicer or page level filter if the whole page is just meant to show /help page data). Then you could add device as a visual filter to the 3 cards showing the percentages.

 

In the image below I pasted your sample data into Power BI, copied in the measure code that I posted earlier and then setup 3 cards using the Device % measure and adding a filter to each for Desktop, Mobile, Table (and I added this as a title to each card)

 

The advantage of this is that you can use this one measure and filter by Page and see the device splits 

 

201908 PageViews.gif 

featleyd Regular Visitor
Regular Visitor

Re: Creating a measure which incorporates filters (slicers)

Hi @d_gosbell 

 

Thanks for your assistance so far.

 

Everytime I try and recreate your measure, I get a result of 1, which of course is not correct.

 

I'm baffled as to why that is happening!

 

Community Support Team
Community Support Team

Re: Creating a measure which incorporates filters (slicers)

Hi @featleyd 

 

I agree with @d_gosbell , attached pbix might helpful to you.

004.PNG

Community Support Team _ Dina Ye
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

Super User
Super User

Re: Creating a measure which incorporates filters (slicers)

It's hard to say without being able to see your model. I've attached my example file to this post if that helps.

 

If not can you reproduce your issue in a small sample file and upload it?

featleyd Regular Visitor
Regular Visitor

Re: Creating a measure which incorporates filters (slicers)

Hello again

 

Thanks again for the download, it has certainly put me in the right direction, however, I think the problem I have now is of my own making.

 

The measure is delivering the total number of pageviews for /help for 2019, and is not interacting with my month slicer.

 

I've added some additional data to the PBIX supplied and the measure ignores the month selector also.

 

The month slicer is populated by it's own table with simply just months in.

There is a relationship between 'Month' and 'Table' in this instance.

Am i missing something from the measure which is prohibiting it with interacting with my month slicer?

 

featleyd Regular Visitor
Regular Visitor

Re: Creating a measure which incorporates filters (slicers)

Thanks again for your help everyone.

 

I've managed to solve it using the following:

 

My pageviews measure was simply 

SUM('Table'[Pageviews])
 
Once I had that, I found it easier to calculate the remaining cards via their own measures, although I completely agree with your suggested method, and that would benefit other users.
 
Thanks again for your help everybody.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 992 guests
Please welcome our newest community members: