cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors