Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VegarOyfoss
Helper I
Helper I

Create a calculated tables with totals based on two columns

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

example1.png

I have then created new calculated tables per KPI using the FILTER option:

example2.png

This works nice for me to create a dashboard like this, which show me average work done by the agents per day:

example3.png

 

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 TimeEffort TimeTickets ClosedTickets TouchedCalls 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

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.

3-1.PNG

Now you will get a table you want.

3-2.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
v-eachen-msft
Community Support
Community Support

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.

3-1.PNG

Now you will get a table you want.

3-2.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

@v-eachen-msft 

 

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 🙂

JarroVGIT
Resident Rockstar
Resident Rockstar

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. 

image.png

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:

image.png

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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.