Help with Matrix - Nested rows (Examples given along with .pbix file)

[Shamatix]

Hi fellow Power Bi users.

So I'll do my best to explain my issue and the goal I want to achieve.

I have also attached a .pbix file in this thread so I hope someone can help me out.

So what I have is two tables.

The first table (Report_InvoicesReceived) contains customers, more specically how many invoices each customer has received daily of each type.

The second table (Report_Customers) contains the "expected amount" of invoices the customer is to receive during a year of each type.

Now I am trying to create a Matrix with 4 columns:

Customer Name
Received Amount
Expected Amount
%

as below:

However I want to be able to click on Company1 and expand it into the various invoice type such as PDFInvoice, EInvoice and TIFInvoice and then see the specific amounts of the various types in ReceivedInvoices and ExpectedTotalInvoices with the correct percentages. 

I have made this Excel screenshot kind of to illustrate it (Each Tab in the "CustomerName" column just illustrates its expanded down into it:

You can download a sample.pbix file on the following link:

https://we.tl/t-Thc4mkU5Y8

Last question goes as follows:
How come there are no data bars in the headers but only if I "drill down" ?

I really hope someone can help me out 🙂

Best regards

[amitchandak]

@Shamatix , For that you need to unpivot three columns Expected - EI, pdf and TI invoices.

https://radacad.com/pivot-and-unpivot-with-power-bi

Also on Databar conditional formatting - Sub total are not supported yet. Log an idea for that.

[Shamatix]

I really cant seem to get that to work, by chance you could show me how in the sample file?

[V-lianl-msft · Accepted solution]

Select these two columns in the query editor, and then click Unpivoted Other Columns.

Data Bar does not support displaying in subtotal or total level at the time being.

You can now apply conditional formatting rules to your totals and subtotals in table and matrix visuals. You will be able to find this new option in the Apply to dropdown in the conditional formatting advanced controls dialog. This feature will work for setting background color, font color, icons, and web URL.

---

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2020-feature-summary/#_Conditional_f...