Row level calculations in Matrix

[Daxxen]

Hi all,

I'm stuck with a request from a co-worker and need help..

I need to create a Matrix visual that has the Provider and Company on the rows. These are coming from Dimension tables "D_Provider" and "D_Company". We also have a "D_Calendar" table connected to the Data table.

The needed measures are Limit, In Use and In Use %.

The row sub totals should not "duplicate" the Limits that are of type Shared.

For type "Shared" the Limit values should not be visible/accounted for in the Matrix other than on row sub totals and grand totals.

For other types the Limit values should be visible.

However, the In Use % should be shown for each company/row in the Matrix (also sub totals and grand totals). 

If the company is using a shared Limit (Type=Shared) then the percentage should be calculated from the shared amount. If not a shared limit then use the amount related to the company. The idea for In Use % should be visible in the table below.

The data is coming from a Excel spreadsheet with the structure below:

Date	Provider	Type	TotalTag	Company	Limit	In Use	In Use %
29.11.2024	Bank1	Shared	Yes	Company 1	1 000 000	200 000	20,00 %
29.11.2024	Bank1	Shared		Company 2	1 000 000	50 000	5,00 %
29.11.2024	Bank1	Shared		Company 3	1 000 000	4 000	0,40 %
29.11.2024	Bank1	Shared		Company 4	1 000 000	30 000	3,00 %
29.11.2024	Bank1	Shared		Company 5	1 000 000	22 000	2,20 %
29.11.2024	Bank2	Own	Yes	Company 1	500 000	150 000	30,00 %
29.11.2024	Bank2	Own	Yes	Company 6	600 000	220 000	36,67 %
29.11.2024	Bank2	Own	Yes	Company 7	700 000	330 000	47,14 %
29.11.2024	Bank3	Own	Yes	Company 1	900 000	400 000	44,44 %
29.11.2024	Bank4	Shared	Yes	Company 1	3 000 000	0	0,00 %
29.11.2024	Bank4	Shared		Company 2	3 000 000	50 000	1,67 %
29.11.2024	Bank5	Own	Yes	Company 1	14 000	14 000	100,00 %
29.11.2024	Not bank	Parent		Company 1	90 000	90 000	100,00 %
31.10.2024	Bank1	Shared	Yes	Company 1	1 000 000	250 000	25,00 %
31.10.2024	Bank1	Shared		Company 2	1 000 000	100 000	10,00 %
31.10.2024	Bank1	Shared		Company 3	1 000 000	54 000	5,40 %
31.10.2024	Bank2	Own	Yes	Company 1	500 000	170 000	34,00 %
31.10.2024	Bank2	Own	Yes	Company 6	600 000	240 000	40,00 %
31.10.2024	Bank2	Own	Yes	Company 7	700 000	350 000	50,00 %
31.10.2024	Bank3	Own	Yes	Company 1	900 000	420 000	46,67 %
31.10.2024	Bank4	Shared	Yes	Company 1	3 000 000	0	0,00 %
31.10.2024	Bank4	Shared		Company 2	3 000 000	55 000	1,83 %
31.10.2024	Bank5	Own	Yes	Company 1	14 000	14 000	100,00 %
31.10.2024	Not bank	Parent		Company 1	90 000	90 000	100,00 %

The output I am looking for is something like this:

Would be extremely grateful for any assistance on this 🙂

[v-tianyich-msft]

Hi @Daxxen ,

Please share your sample file so that the problem may be solved better.

Best regards,
Community Support Team_ Scott Chang

[lbendlin]

[Daxxen]

Hi and many thanks for the idea.

However, this solution does not work because the report page contains a year and month filter from D_Calendar that is used to filter also other visualizations on the page. Maybe I was not clear enough when saying that the data table is connected to a calendar.

Any suggestions on how to incorporate the usage of the Date dimension?

Also, the Provider and Company are coming from their own dimension tables as mentioned in the original post. It is required to use these dimensions since the end-user wants to show the providers and companies in a specific order and these sort orders are handled by a sorting column in the dimensions.