Forum Discussion

NishPatel

Resolver II

4 years ago

Solved

Two column with grouped rows

Hi, Is it possible to create below matrix in power BI?

Need Help

PaulDBrown
4 years ago
NishPatel

Thanks for that. It really helps.

Caveat: this example follows the criteria laid out in your latest data sample. In other words, the % split by cost type is the same for each company. If the split is different for each company, we need a table with the detailed % split by cost type and by company to make the correct calculations

Ok, here is one way. You need to create intermediate tables in Power Query for the % calculations (which involves custom - calculated- columns and unpivotting) to finally append them all in a final table. Just beware that every cost type must have a % split summing to 100% (I've added a new calulation in the Spare Parts Table costs for the 80% not accounted for in your example). This way the sum of cost breakdown will equal the sum of the corresponding amount.

The table looks like this:

You can then use the Cost Type and Cost Breakdown fields to structure the matrix. The measure is a simple sum:

By cost type

By company

I've attached the sample PBIX file
Simple Matrix layout 1.pbix54 KB

NishPatel

Resolver II

4 years ago

Hi Amit,

I am not sure how to upload a file here and that's why i pasting below sample raw data. I appreciate your help.

Year	Month	Amount	Type	Company	InvoiceNo
2021	Apr	205	X	Company A	001
2021	Apr	627	Y	Company A	001
2021	Apr	583	Z	Company A	001
2021	Apr	738	X	Company B	001
2021	Apr	160	Y	Company B	001
2021	Apr	374	Z	Company B	001
2021	Apr	291	X	Company C	001
2021	Apr	442	Y	Company C	001
2021	Apr	769	Z	Company C	001
2021	Apr	647	X	Company A	002
2021	Apr	954	Y	Company A	002
2021	Apr	153	Z	Company A	002
2021	Apr	185	X	Company B	002
2021	Apr	983	Y	Company B	002
2021	Apr	923	Z	Company B	002
2021	Apr	233	X	Company C	002
2021	Apr	297	Y	Company C	002
2021	Apr	149	Z	Company C	002
2021	May	314	X	Company A	003
2021	May	747	Y	Company A	003
2021	May	784	Z	Company A	003
2021	May	438	X	Company B	003
2021	May	540	Y	Company B	003
2021	May	223	Z	Company B	003
2021	May	426	X	Company C	003
2021	May	640	Y	Company C	003
2021	May	360	Z	Company C	003
2021	May	907	X	Company A	004
2021	May	879	Y	Company A	004
2021	May	865	Z	Company A	004
2021	May	603	X	Company B	004
2021	May	622	Y	Company B	004
2021	May	223	Z	Company B	004
2021	May	580	X	Company C	004
2021	May	953	Y	Company C	004
2021	May	682	Z	Company C	004
2021	Jun	142	X	Company A	005
2021	Jun	122	Y	Company A	005
2021	Jun	887	Z	Company A	005
2021	Jun	510	X	Company B	005
2021	Jun	925	Y	Company B	005
2021	Jun	614	Z	Company B	005
2021	Jun	563	X	Company C	005
2021	Jun	230	Y	Company C	005
2021	Jun	240	Z	Company C	005
2021	Jun	144	X	Company A	006
2021	Jun	679	Y	Company A	006
2021	Jun	216	Z	Company A	006
2021	Jun	257	X	Company B	006
2021	Jun	820	Y	Company B	006
2021	Jun	928	Z	Company B	006
2021	Jun	309	X	Company C	006
2021	Jun	868	Y	Company C	006
2021	Jun	853	Z	Company C	006
2021	Jul	349	X	Company A	007
2021	Jul	853	Y	Company A	007
2021	Jul	664	Z	Company A	007
2021	Jul	543	X	Company B	007
2021	Jul	829	Y	Company B	007
2021	Jul	700	Z	Company B	007
2021	Jul	387	X	Company C	007
2021	Jul	789	Y	Company C	007
2021	Jul	382	Z	Company C	007
2021	Jul	172	X	Company A	008
2021	Jul	113	Y	Company A	008
2021	Jul	657	Z	Company A	008
2021	Jul	577	X	Company B	008
2021	Jul	201	Y	Company B	008
2021	Jul	691	Z	Company B	008
2021	Jul	889	X	Company C	008
2021	Jul	935	Y	Company C	008
2021	Jul	620	Z	Company C	008
2021	Aug	239	X	Company A	009
2021	Aug	216	Y	Company A	009
2021	Aug	586	Z	Company A	009
2021	Aug	748	X	Company B	009
2021	Aug	759	Y	Company B	009
2021	Aug	848	Z	Company B	009
2021	Aug	309	X	Company C	009
2021	Aug	202	Y	Company C	009
2021	Aug	118	Z	Company C	009
2021	Aug	770	X	Company A	010
2021	Aug	777	Y	Company A	010
2021	Aug	738	Z	Company A	010
2021	Aug	476	X	Company B	010
2021	Aug	1000	Y	Company B	010
2021	Aug	763	Z	Company B	010
2021	Aug	194	X	Company C	010
2021	Aug	458	Y	Company C	010
2021	Aug	902	Z	Company C	010

PaulDBrown

Community Champion

4 years ago

Ideally (best practices) you sould create dimension tables for the non-value columns (those you will be using to filter by). In this example I've only created a dimension table for month (to ensure proper sorting)

Then with a simple sum measure

Sum Amount = SUM(FactTable[Amount])

and this structure for a matrix visual

and drilling down on rows and columns

you get

NishPatel

Resolver II

4 years ago

Hi Paul,

First of all thank you for helping me out here. But i forgot to mention, I have 3 calculated columns (% split from the amount column) derived from the Amount column and I need to show calculated amount and not the original amount.

Thanks in advance

PaulDBrown

Community Champion

4 years ago

Sorry, I'm not following. You can use any measure in the matrix by adding it to the Values bucket.

NishPatel

Resolver II

4 years ago

Hi Paul,

First of all sorry for not explaining this properly. I have created 3 tables filtered by 3 different companies from the main table as shown below. After that I have created a calculated column (Not a measure) to split the amount column by % into 3 columns as shown below sample for company A. I need to show the split amount and not the original amount in the way you explained in your earlier post.

Year	Month	Amount	Type	Company	InvoiceNo	Split A (25%)	Split B (35%)	Split C (40%)
2021	Apr	205	X	Company A	001	51.25	71.75	82
2021	Apr	627	Y	Company A	001	156.75	219.45	250.8
2021	Apr	583	Z	Company A	001	145.75	204.05	233.2
2021	Apr	647	X	Company A	002	161.75	226.45	258.8
2021	Apr	954	Y	Company A	002	238.5	333.9	381.6
2021	Apr	153	Z	Company A	002	38.25	53.55	61.2
2021	May	314	X	Company A	003	78.5	109.9	125.6
2021	May	747	Y	Company A	003	186.75	261.45	298.8
2021	May	784	Z	Company A	003	196	274.4	313.6
2021	May	907	X	Company A	004	226.75	317.45	362.8
2021	May	879	Y	Company A	004	219.75	307.65	351.6
2021	May	865	Z	Company A	004	216.25	302.75	346
2021	Jun	142	X	Company A	005	35.5	49.7	56.8
2021	Jun	122	Y	Company A	005	30.5	42.7	48.8
2021	Jun	887	Z	Company A	005	221.75	310.45	354.8
2021	Jun	144	X	Company A	006	36	50.4	57.6
2021	Jun	679	Y	Company A	006	169.75	237.65	271.6
2021	Jun	216	Z	Company A	006	54	75.6	86.4
2021	Jul	349	X	Company A	007	87.25	122.15	139.6
2021	Jul	853	Y	Company A	007	213.25	298.55	341.2
2021	Jul	664	Z	Company A	007	166	232.4	265.6
2021	Jul	172	X	Company A	008	43	60.2	68.8
2021	Jul	113	Y	Company A	008	28.25	39.55	45.2
2021	Jul	657	Z	Company A	008	164.25	229.95	262.8
2021	Aug	239	X	Company A	009	59.75	83.65	95.6
2021	Aug	216	Y	Company A	009	54	75.6	86.4
2021	Aug	586	Z	Company A	009	146.5	205.1	234.4
2021	Aug	770	X	Company A	010	192.5	269.5	308
2021	Aug	777	Y	Company A	010	194.25	271.95	310.8
2021	Aug	738	Z	Company A	010	184.5	258.3	295.2

Forum Discussion

Two column with grouped rows

Related content

Grouping rows by Column and then updating Column

Create columns group

Grouping Columns

Fabric & Azure Data User Group Program Requirements & Benefits

Matrix column not grouped