Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am attempting to show the top 5 customers per month, based on the number of tickets they have submitted, over a rolling 12 month period in the format of a stacked column chart.
I naively thought I could create a measure that would do a distinct count of ticket id's, which could then be used as a value, with the Month-YYYY listed as the axis, with a legend of the customer name and limit the customer's to the top 5 based on the disctinct count of tickets. This has, in all fairness, done exactly as I have asked it to do, however, this isn't actually displaying what I set out to acheive.
It seems that the count of distinct tickets (ticket_count = DISTINCTCOUNT(ticket[id])) doesn't behave or belong to the context of each month, it is instead, getting the top 5 customers by ticket count (all time) and splitting their values by each date period.
I need to somehow create a measure that measures the top 5 for the month, in the context of the bottom 12 month rolling axis, as opposed to in the context of "all time". I would expect a different set of customers to be show each month, rather than the top 5 all time split by the month/year - the immediate outcome of this was that it was clearly not doing this as it showed the same 5 customers each month.
I beleive I need to use the RANKX function and somehow rank this by the month/year axis - I am just not sure if I am on the correct lines there? Any thoughts?
ds
Solved! Go to Solution.
Hi @dspbi ,
Yes,you need "RANKX" function,for details,pls see below:
I made a simple sample as below:
Above is an original table;
Then create a measure as below:
Measure = RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,DESC)
In the filter pane of stacked column chart,select is less than or equal to 5, and you will see:
For my sample .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @dspbi ,
Yes,you need "RANKX" function,for details,pls see below:
I made a simple sample as below:
Above is an original table;
Then create a measure as below:
Measure = RANKX(ALL('Table'),CALCULATE(SUM('Table'[value])),,DESC)
In the filter pane of stacked column chart,select is less than or equal to 5, and you will see:
For my sample .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thank you for coming back to me on this and providing a sample file.
I have worked this in to my report and it seems to work great.
I am going to do some further testing to ensure all is fine but I will mark your solution as accepted in the mean time.
Thank you again for coming back to me.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |