Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @Anonymous
I agree with @d_gosbell , attached pbix might helpful to you.
Thanks again for your help everyone.
I've managed to solve it using the following:
My pageviews measure was simply
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]
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
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.
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?
Thanks again for your help everyone.
I've managed to solve it using the following:
My pageviews measure was simply
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] ) )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |