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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
giramswa
Helper II
Helper II

Display Total in Matrix visual row

Hi All,

 

I have a table with Project data as below:

CustomerProject_NumProject_StatusAllotted_HrsUsed_HrsRemaining_HrsEmployees_Assigned
C1P1Open1005050Emp1
      Emp2
 P2Closed200100100null
 P3Closed300150150null
C2PM1Open15050100Emp3
      Emp4
 PM2Open250100150Emp5
      Emp6
 PM3Cancelled350200150null
C3PRJ_1Open1000500500Emp7
 PRJ_2Closed20001200800null
 PRJ_3Open30002600400Emp8
      Emp9

 

I want to display above data in a matrix visual as below:

CustomerTotal Alloted_HrsTotal Used_HrsTotal Remaining_HrsProject_StatusProject_NumNum_Of_Employees
C1600300300OpenP12
    ClosedP20
     P30
C2750350400OpenPM12
     PM22
    CancelledPM30
C3600043001700OpenPRJ_11
     PRJ_32
    ClosedPRJ_20

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?

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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"

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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"

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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