Show columns and values on rows in same matrix

[KevinColes]

Hi experts,

I am wondering if anyone knows if this is possible or how to do it. I am building a Work In Process (WIP) report based off of ERP data that includes Aging columns for the Unbilled amounts. In the native application we can run SSRS based reports that shows the data like this:

The aging columns are basically just filtered versions of the summarized Billing Amount by days. The row based totals for Labor, Expense and Units can be handled by filtering on the source table of the data. I am able to get all of this data but the only way I have been able to re-produce this in Power BI is in a matrix with all of these items as columns. I need to have a hierarchy of Company Sector, Project Manager and Project such that the user can see a rollup or drill down to the detail (some slicers will be added as well.

So far I have come up with a matrix like this:

The problem with this is it doesn't give the aging of the breakout (Labour / Cons / Exp / Unit). I know I will be asked to try to replicate what the SSRS does. I think what I need maybe is an Unpivot of the first 5 columns but I suspect it's not possible to combine columns and rows in a matrix. If I show this with Values on Rows it doesn't give a good result. 

Also all of the columns in the second screen shot are measures based on filtered versions of the Total Unbilled column in the data set. I could create those 5 columns as individual columns in my query if needed but the Aging columns have to be measures based on aging of the Transaction date against the current date (Today()).

Here is a snippet of what the data looks like:

Any advice on how to achieve the format of the first screenshot would be greatly appreciated but if there is definitely no way to acheive that I need to know that as well so I can explain it to the decision makers.

Thanks,

Kevin 

[KevinColes]

OK so apologies for not updating this sooner. I was quite successful in finding an "Aging Bucket Column" method to re-create the SSRS report in Power BI. I won't get into the minutae of how I was able to set this up but anyone interested can messsage me for help on that. 

Here is a link to an anonymized version of the report: https://klcoles-my.sharepoint.com/:u:/p/kevin/EZi6bxKDN3xCvOcF7wR0UsABMeJIi2zt_ciJTv5iwN2eGA?e=fF0yE...

I know have 2 more issues I'm hoping someone can help with:

Problem 1: Using this bucketing system, the columns are actually dynamic and as such I seem to have lost the ability to sort them when published. This is quite important because the users will want to sort on the oldest WIP in days to quickly identify it. Or perhaps someone can suggest some other option? Maybe a quick aging filter?

Problem 2: On the original SSRS report, the Held rows (Held Labor, Expense and Units) would only display if there wa data for the row. Is there a way I can accomplish this in Power BI?

Thank you in advance!

Kevin

Below is a screenshot of how it looks now in Power BI:

[vanessafvg]

the easiest thing to do is anonymise some of your data and provide a small sample.  That way it would be easy to work it out.  However In power query i would think it quite simple to unpivot the measures that you currently have into a different structure.

Power query challenge -Unpivot complex tables solution

https://www.youtube.com/watch?v=l8eh6uOYa0g