Forum Discussion
Ranking Visualisation
Hi,
I've been learning DAX and powerbi the past few days and I've heard the suggestion to have a seperate date table as so it's easier to work with.
I have my seperate table for dates setup, my original data and my visual displaying my data in the right date order.
In a new visual, I'd like to show the top 10 days ranked by the number of sessions.
I've tried using:
Rank Top 10 = RankX(All('Table123'[DateKey]), [Sales12345] )Where [Sales12345] is my Sum of my total sessions.
Thoughts?
https://gyazo.com/5905a48e86e722869789d7d2daf70b5c
On the left is my number of sessions organised by month from the beginning of data. On the right is my current visualisation attempt.
https://gyazo.com/622a26a17c1866c48e3b115de7125940
Is my date table with the months/days specially done so I can have my graph start from the beginning of the data and order in the proper manner.
https://gyazo.com/1638781456c5e8b78ae27d4bd2c19170
Relationships. Sessions on the left is linked with Datekey on the right.
Right; I've sorted it.
Very Useful link which for some reason worked when i tried it this time around: http://www.dutchdatadude.com/power-bi-pro-tip-show-top-x-results-with-rankx-function/
You do this by having your time as your axis. Your value as the number of (mine was number of sessions straight from my original dataset).
You create two measures;
First is:
Number of Sessions = SUM('All Web Site Data'[Sessions])For the Number of Sessions.
Second is:
Top10-2 = RANKX(ALL('All Web Site Data'[Sessions]), [Number of Sessions])Which ranks all of the number of sessions, by the total number of sessions.
You then apply the Second Measure to the Visual Level Filters and set it to "is less than or equal to" 10. I chose as I wanted to see the top 10, change as needed.
Very cool; Loving powerbi but working through these things are incredibly time consuming (amazing forum btw)
2 Replies
I've also tried to use the TOPN function to no luck;
https://gyazo.com/04cf5a1c4d92d8fc4db2feb51204dedc
Right; I've sorted it.
Very Useful link which for some reason worked when i tried it this time around: http://www.dutchdatadude.com/power-bi-pro-tip-show-top-x-results-with-rankx-function/
You do this by having your time as your axis. Your value as the number of (mine was number of sessions straight from my original dataset).
You create two measures;
First is:
Number of Sessions = SUM('All Web Site Data'[Sessions])For the Number of Sessions.
Second is:
Top10-2 = RANKX(ALL('All Web Site Data'[Sessions]), [Number of Sessions])Which ranks all of the number of sessions, by the total number of sessions.
You then apply the Second Measure to the Visual Level Filters and set it to "is less than or equal to" 10. I chose as I wanted to see the top 10, change as needed.
Very cool; Loving powerbi but working through these things are incredibly time consuming (amazing forum btw)