cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

Hi @Anonymous 

 

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

Anonymous
Not applicable

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
v-diye-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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!


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

Anonymous
Not applicable

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!

 

Hi @Anonymous 

 

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

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?

Anonymous
Not applicable

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?

 

Anonymous
Not applicable

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

d_gosbell
Super User
Super User

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.