I have waitlist data that is reported daily for a number of services, and rated by priority.
We have a monthly target to achieve, with a separate target for each priority (Urgent, Semi-Urgent, Non-Urgent).
I have set up the following tables in the Data Model:
- a Date Table,
- a table for the Waitlist data (linked to Date table), and
- three tables for Urgent, Semi-Urgent, and Non-Urgent Targets with a month and target for the month (also linked to the date table).
I am then reporting separately for each priority of the waitlist by applying a Priority filter on Waitlist Pivot Table (as well as doing YTD using calculated fields).
(Filter) Priority:Urgent
Actual Target KPI
Jan
Feb
...
Where I am getting into trouble is setting up a KPI.
I report actual waitlist numbers (filtered by say Urgent Priority), Target Waitlist numbers (Urgent Priority Targets). I can set a KPI against actual numbers and get my first Pivot Table looking good.
However, when I go to set up the next table filtered by say Semi-Urgent I cannot set a separate KPI against actual waitlist numbers for this priority as I have already used up this KPI for "Urgent" Priority.
In other words I need to set up individual KPI's against each priority, not against the total.
I could use Power Query to filter incoming data into three separate tables and achieve the result that way but I think there must be a simpler solution.
Is there a workaround or better way of setting up my data?
Any thoughts appreciated.
Thanks
Peter
Hi Peter,
It'd be ideal if you can create a sample file containing some data or dummy data that mimics the structure of your model. At the moment I don't know what columns you have in your waitlist data, or the columns in your 3 target tables, other than date and value.
The target tables probably should be consolidated into one. The target table then needs a relationship with the waitlist table.
However, I can't be specific (or sure) without knowing the structure of your model.
Mynda
Thanks Mynda,
I have pulled my existing pivots apart now experimenting, but I have enclosed a screenshot, and sample input data
Hi Peter,
Thanks for sharing the file and screen shots. I think there are still some pieces missing from the puzzle, but I've created some KPI's in the attached file.
One thing you must be aware of is you can only create a KPI against a measure, so if you want to use the same data twice, or a portion of that data then you should create an explicit measure that returns those values.
You'll see in the file attached that I have created explicit measures for the different 'days waiting' and applied the filter in the measure, as opposed to using the Filters field.
I hope that makes sense.
Mynda
Thanks for your help Mynda, I will take a look.
Thanks Mynda, that is great and much simpler than my workaround. I had missed the Priority/Status linkage between the target and data which was my main hold up, but your other tips make the process much simpler.
Great work.
Peter
Great. Glad you're on the right track now.
