Visualizing a source table as a Pivoted table (similar to Matrix visual) with custom columns

[DanJipa]

Hello,
Relatively new to Power BI. I've been struggling with this problem for well over a week now, and after thoroughly experimenting and trying to do it by myself, I gave up. Let me described the context real quick: I have a source table which contains every single record of my team members logging in time (time tracking table). For example let's say we have 4 employees who all have logged their time for today in our time tracking application. The source table would look like this (I only showcase the most important columns):

TestType represents the type of activity they spent their time on, Hours means the amount of hours they spent on it, Date represents the exact time when they logged their time in the app and Shift represents their work Shift. The above table represents the time logged for one day(this is done every day).
What I need to do in Power BI is to represent all of the data above like this:

Now, excluding the last 3 columns (Total, Planned and Delta), I already know how to do, because it's basically just a Matrix Visual. The problem I faced when trying to do this with a Matrix visual is that I cannot add those 3 calculated columns at the end. I need Total to calculate the total for each employee, Planned will be a Fixed values columns based on which shift the employee is on (6.5 hours for Nightshift, 7.5 for Dayshift) and Delta is just Total - Planned. Sounds pretty simple, but I just cannot do this using the Matrix Visual without making it look very ugly and non-user friendly. I just want to maintain the look of a table, and Matrix is not really helping because I have to drill down and then it looks way off. I want the end-user to be able to just open the report and see the data directly like in the above picture. I tried transforming the Source table into a Pivot table, grouping by Employee name, but then I lose the Date column for every record, and I need that to be able to use Slicers on the table (week, day, hour slicers). So basically I would need my source table to remain the way it is, but then transform it in a Matrix-like visual with added custom columns without ruining the aspect and user experience when viewing it. I got really close one time using Matrix visual, I managed to add the Planned column but then when I wanted to add Total and Delta I got stuck. Any help or other aproaches would be much appreciated. Thanks!

[v-yuta-msft · Accepted solution]

@DanJipa ,

Matrix visual and 'Pivot Columns' in tranform panel don't work. You can use python to generate such a table. The code will be like pattern below:

# 'dataset' holds the input data for this script

import pandas as pd

dict1 = dict(zip(dataset['Name'], dataset['Shift']))
dict2 = {'Nightshift': 6.5, 'Dayshift': 7.5}

dataset = dataset.pivot(index = 'Name', columns = 'TestType', values = 'Hours').fillna(0)
Name = dataset.index.tolist()
dataset.insert(0, "Name", Name)
dataset['Total'] = dataset.sum(axis = 'columns')
dataset['Planned'] = dataset['Name'].map(dict1).map(dict2)
dataset['Delta'] = dataset['Total'] - dataset['Planned']

 

You can also refer to the pbix file.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Seem like you need a hybrid table. You can only have it if you limited test type and you create them as measures.

Please vote for

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39773011-hybrid-table