Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi All,
I have a table with Project data as below:
Customer | Project_Num | Project_Status | Allotted_Hrs | Used_Hrs | Remaining_Hrs | Employees_Assigned |
C1 | P1 | Open | 100 | 50 | 50 | Emp1 |
Emp2 | ||||||
P2 | Closed | 200 | 100 | 100 | null | |
P3 | Closed | 300 | 150 | 150 | null | |
C2 | PM1 | Open | 150 | 50 | 100 | Emp3 |
Emp4 | ||||||
PM2 | Open | 250 | 100 | 150 | Emp5 | |
Emp6 | ||||||
PM3 | Cancelled | 350 | 200 | 150 | null | |
C3 | PRJ_1 | Open | 1000 | 500 | 500 | Emp7 |
PRJ_2 | Closed | 2000 | 1200 | 800 | null | |
PRJ_3 | Open | 3000 | 2600 | 400 | Emp8 | |
Emp9 |
I want to display above data in a matrix visual as below:
Customer | Total Alloted_Hrs | Total Used_Hrs | Total Remaining_Hrs | Project_Status | Project_Num | Num_Of_Employees |
C1 | 600 | 300 | 300 | Open | P1 | 2 |
Closed | P2 | 0 | ||||
P3 | 0 | |||||
C2 | 750 | 350 | 400 | Open | PM1 | 2 |
PM2 | 2 | |||||
Cancelled | PM3 | 0 | ||||
C3 | 6000 | 4300 | 1700 | Open | PRJ_1 | 1 |
PRJ_3 | 2 | |||||
Closed | PRJ_2 | 0 |
Reason for using Matrix is the requirement where we need to show total hrs per customer, group all projects with similar status under each customer. Currently I have placed Customer, Project Status, Project Num under rows.
Matrix visual does not allow placing a measure under Rows. 'Switch values to rows' will show every value under row which I don't want. How can I achieve this?
Solved! Go to Solution.
Your sample data is not in a usable format. Please confirm that these transforms are still correctly representing the data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCsIwEER/RXLuodm0tZ6DF6EYvJYgornFWBD/302WZqMEesikhcmbnZ1noaVohIlyXlzAS7Ytap/l+FyksM0sdvhTO2iAbDCAov3r7R74AYklCw0f79msSrMiW8+azTpSzVROyQMSGKdQW2N2nDwBs6DAyLVzvwUbCljqcQt35z1VSRRoa1Wi11xO19+VUxtWDNhzANr/95rQlDBWNosvFAco8sOQrm5NGLc6HoS1Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Project_Num = _t, Project_Status = _t, Allotted_Hrs = _t, Used_Hrs = _t, Remaining_Hrs = _t, Employees_Assigned = _t]),
#"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value",{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"})
in
#"Filled Down1"
Your sample data is not in a usable format. Please confirm that these transforms are still correctly representing the data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBCsIwEER/RXLuodm0tZ6DF6EYvJYgornFWBD/302WZqMEesikhcmbnZ1noaVohIlyXlzAS7Ytap/l+FyksM0sdvhTO2iAbDCAov3r7R74AYklCw0f79msSrMiW8+azTpSzVROyQMSGKdQW2N2nDwBs6DAyLVzvwUbCljqcQt35z1VSRRoa1Wi11xO19+VUxtWDNhzANr/95rQlDBWNosvFAco8sOQrm5NGLc6HoS1Xw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Project_Num = _t, Project_Status = _t, Allotted_Hrs = _t, Used_Hrs = _t, Remaining_Hrs = _t, Employees_Assigned = _t]),
#"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"}),
#"Filled Down1" = Table.FillDown(#"Replaced Value",{"Customer","Project_Num","Project_Status","Allotted_Hrs","Used_Hrs","Remaining_Hrs"})
in
#"Filled Down1"
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!