Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone
I have an issue I am not able to find a solution for. My source consists of aggregated amount of work done by agents per day for several KPIs , ex AVG talk Time, Effort Time, Tickets Closed, Tickets Touched etc etc. All of this data is provided in one table, and each row contains one agents for for on day for one KPI. In other words all KPIs are in the same row called "KPIName".
Example
I have then created new calculated tables per KPI using the FILTER option:
This works nice for me to create a dashboard like this, which show me average work done by the agents per day:
As a next step, and this is where I am not able to find a solution, is to create another table which consists of the totals per KPI per Month, something like this:
Month | AVG Talk Time | Effort Time | Tickets Closed | Tickets Touched | Calls Accepted |
October | |||||
November |
The Dashboard above shows me the average on the people who were at work, but we always have people on sickness/vacation/training etc etc. So I want to see what the avg workload whould have been if all agents would be present. I could then use the above table and caclulate workload with actuall hired staff, and not only those who are in the office
I have tried some SUM and SUMMARZISE but I have not been succsessful in capturing it with the two conditions "month" and "kpi"
i hope this makes sense. Thanks for any help you can provide
Solved! Go to Solution.
Hi @VegarOyfoss ,
You could copy your orifinal table in the query editor and keep "KPIName","Numerator" and "Month Name" column ( remove other columns ) in your new table.
Then pivot the "Month Name" column.
Now you will get a table you want.
Hi @VegarOyfoss ,
You could copy your orifinal table in the query editor and keep "KPIName","Numerator" and "Month Name" column ( remove other columns ) in your new table.
Then pivot the "Month Name" column.
Now you will get a table you want.
Thank you
This seem to do the trick. Although I did reference to the original table instead of duplicate it (it consists of 14 mill rows). Also, when i pivot the Month column, i got the oposite results of you and had months in separate columns. When I pivot on the KPIName instead, I was able to get all months in one column downwards 🙂
The table you are showing is actually not that hard to achieve, I think. Wouldn't you be able to use a Measure on the main table (not the calculated ones)?
I've reused a table I created for another question. Timestamp column is your Date column, Value is your KPI Value column and Group is your KPI column.
With this table, resembling your original dataset (I hope you can see the relevant similarities), I am able to create a Measure like this:
AvgPerMonthAndGroup = CALCULATE(AVERAGE(Table1[Value]), Table1)
Now, creating a Matrix visual will achieve what you are looking for. In my example, I am using Timestamp (day) for Rows, Group for columns and my measure for values. This result in the following visual:
Hope this helps you out! 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |