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
KevinColes
Helper III
Helper III

Trying to indent child rows of matrix Hierarchy

Hi experts,

 

I have a flat data source coming from SQL that is based off of individual time records and we do aggregations in Power BI and group into different labor types. I cannot change the data source at this point so I have to work with it like this. The data is Sums of Dollar amounts associated to underlying Labor records. I have a value for Total Labor Revenue which is then  broken down into Engineering Labor, Planning Labor, Survey Labor, Capital Works Labor and Architectural Labor. 


I want to show with each on a matrix row with Columns for accounting periods (month/year) where the amounts show. I can get this to work fine however I've been asked to show the breakout rows of labor to be indented or even better collapsed under Total Labor Revenue which I could then expand. I may need to do this for some other metrics as well so if I can collpase/expand this would simplify the report for the users and those who are keen to see breakout can drill down. 


I tried creating a hierarchy but it only seems to work if I put it on the Rows but this is the wrong layout and I lose my row headers.

The first screenshot is what I can achieve now without issue:

KevinColes_0-1593814518448.png

I achieved the above using this:

 

KevinColes_1-1593814630334.png

 

What I would like to see at minimum is something like this:

KevinColes_2-1593815015569.png

Whereby my row headers for the labor breakout are indented to signify they are in fact children of the total. This will help end users understand it more clearly. 

 

Or preferably we would want a collapse / expand something like this:

KevinColes_3-1593815254447.png          KevinColes_4-1593815282465.png

I've tried all sorts of different ways that I've read but can't quite get it to work. It seems to not even be possible just to format the row headers individually to be indented.

 

Any help you can provide is much appreciated as I've gotten stuck on this and need to move on to some additional things. Solving this would be a big win moving forward for us.

Thanks,


Kevin Coles

 

 

 

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

Hi @KevinColes ,

Could you please check if the below screen shot is your expected result? If yes, first you can select all labour value with different types and unpivot these columns. Then create a matrix. I created a sample pbix file, you can get it from this link.

Expected result??Expected result??Unpivot labour values and create matrixUnpivot labour values and create matrixBest Regards

Rena

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

Hi @v-yiruan-msft,

 

I get an "Object reference not set to an instance of an object" error trying to open your PBIX:

 

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

Stack Trace:
at Microsoft.Mashup.Host.Document.PackageValidationResult.Merge(PackageValidationResult original, PackageValidationResult content)
at Microsoft.PowerBI.Client.Windows.Services.ReportFactory.CreateReportFromPowerBIPackage(IPowerBIPackage powerBIPackage, String pbixFilePath, Byte[] securityBindings, Boolean requiresConversionToReportLayout, PowerBIPackageOpenOptions options, IPowerBITelemetryService powerBITelemetryService, IAnalysisServicesService analysisServices, Version pbixFileVersion, PackageValidationResult& mashupValidationResult)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIPackagingService.Open(FileStream fileStream, IPowerBIWindowService windowService, Nullable`1 entryPoint, PowerBIPackageOpenOptions options, ReportPreparerResult& prepareResult)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.<LoadFromPbix>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c__DisplayClass15_0`1.<<BlockUIAndRun>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion(Task task)
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.BlockUIAndRun[T](Func`1 asyncMethod)
at Microsoft.PowerBI.Client.Windows.Services.FileManager.OpenFile(IPowerBIWindowService windowService, IPbixFile fileToOpen, Nullable`1 entryPoint)
at Microsoft.PowerBI.Client.Program.TryOpenOrCreateReport(IUIHost uiHost, ISplashScreen splashScreen, IFileManager fileManager, IFileHistoryManager fileHistoryManager, String filePath, FileType fileType)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass2_0.<Main>b__1()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Object reference not set to an instance of an object.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

Can anyone help with this? I'm still stuck.

Thanks!

Hi @v-yiruan-msft

Something else I should point out.....what I am trying to do here is part of a larger data set whereby I have other individual metrics within the same visual....many are not psuedo-hierarchies.....and I also have 2 other sections that will need to follow the same look of indentation and/or collapse/expand sections. 

 

Here is a screenshot of the actual expected result as taken from the spreadsheet that I'm trying to replace with PowerBI. 90% of the rows are already in my visual and now I'm trying to take it to that next level with indentation or with a hierarchy (preferred):

 

KevinColes_0-1594049791474.png

 

If what I'm trying to achieve isn't possible in a single visual then I can probably break it out and have them stacked on the dashboard.


Thanks,


Kevin

Hi @v-yiruan-msft,

 

Thank you for this suggestion....that is the look I want but I can already achieve that look without doing the unpivot. What I'm hoping to achieve is what I showed in the Excel screenshot.....Total Labor Revenue (which is not an actual total line but rather a column in the data set) on top, with the breakout labor values below. We then need those breakouts to be indented or even better to have them be a hierarchy under the Total Labor Revenue. All of the values, including total, are Sums based off of time card records for a given period. 

 

I will take a look at the .pbix and get back to you ASAP.


Thanks,


Kevin

harshnathani
Community Champion
Community Champion

Hi @KevinColes ,

 

See if this helps

 

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

 

Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

lbendlin
Super User
Super User

Don't use a hierarchy

put the measures into the values area

in the settings for values select "show on rows"

Hi there,

 

That is exactly what I have done....whether I use the hierarchy as shown in my screen shot or the measures individually they still display as I have shown. This is the layout I need, but I still want to either have the child members indented or collpased in this format.


Thanks!

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.