cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NishPatel
Frequent Visitor

Two column with grouped rows

Hi,

Is it possible to create below matrix in power BI?

NishPatel_0-1634653324920.png

 

1 ACCEPTED SOLUTION

@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:

FinalTable.JPG

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

By cost typeBy cost type

 

By companyBy company

 

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
amitchandak
Super User
Super User

@NishPatel , if X,Y,Z are measures or values of column and 001 , 002 are also values of a column. this should work(similar, not same) .

 

But you need to share sample raw data.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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.

 

YearMonthAmountTypeCompanyInvoiceNo
2021Apr205XCompany A001
2021Apr627YCompany A001
2021Apr583ZCompany A001
2021Apr738XCompany B001
2021Apr160YCompany B001
2021Apr374ZCompany B001
2021Apr291XCompany C001
2021Apr442YCompany C001
2021Apr769ZCompany C001
2021Apr647XCompany A002
2021Apr954YCompany A002
2021Apr153ZCompany A002
2021Apr185XCompany B002
2021Apr983YCompany B002
2021Apr923ZCompany B002
2021Apr233XCompany C002
2021Apr297YCompany C002
2021Apr149ZCompany C002
2021May314XCompany A003
2021May747YCompany A003
2021May784ZCompany A003
2021May438XCompany B003
2021May540YCompany B003
2021May223ZCompany B003
2021May426XCompany C003
2021May640YCompany C003
2021May360ZCompany C003
2021May907XCompany A004
2021May879YCompany A004
2021May865ZCompany A004
2021May603XCompany B004
2021May622YCompany B004
2021May223ZCompany B004
2021May580XCompany C004
2021May953YCompany C004
2021May682ZCompany C004
2021Jun142XCompany A005
2021Jun122YCompany A005
2021Jun887ZCompany A005
2021Jun510XCompany B005
2021Jun925YCompany B005
2021Jun614ZCompany B005
2021Jun563XCompany C005
2021Jun230YCompany C005
2021Jun240ZCompany C005
2021Jun144XCompany A006
2021Jun679YCompany A006
2021Jun216ZCompany A006
2021Jun257XCompany B006
2021Jun820YCompany B006
2021Jun928ZCompany B006
2021Jun309XCompany C006
2021Jun868YCompany C006
2021Jun853ZCompany C006
2021Jul349XCompany A007
2021Jul853YCompany A007
2021Jul664ZCompany A007
2021Jul543XCompany B007
2021Jul829YCompany B007
2021Jul700ZCompany B007
2021Jul387XCompany C007
2021Jul789YCompany C007
2021Jul382ZCompany C007
2021Jul172XCompany A008
2021Jul113YCompany A008
2021Jul657ZCompany A008
2021Jul577XCompany B008
2021Jul201YCompany B008
2021Jul691ZCompany B008
2021Jul889XCompany C008
2021Jul935YCompany C008
2021Jul620ZCompany C008
2021Aug239XCompany A009
2021Aug216YCompany A009
2021Aug586ZCompany A009
2021Aug748XCompany B009
2021Aug759YCompany B009
2021Aug848ZCompany B009
2021Aug309XCompany C009
2021Aug202YCompany C009
2021Aug118ZCompany C009
2021Aug770XCompany A010
2021Aug777YCompany A010
2021Aug738ZCompany A010
2021Aug476XCompany B010
2021Aug1000YCompany B010
2021Aug763ZCompany B010
2021Aug194XCompany C010
2021Aug458YCompany C010
2021Aug902ZCompany C010

 

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)

Model.JPG

Then with a simple sum measure

Sum Amount = SUM(FactTable[Amount])

and this structure for a matrix visual

structure.JPG

 and drilling down on rows and columns

drill1.gif

 

 

you get

 

result.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

 

YearMonthAmountTypeCompanyInvoiceNoSplit A (25%)Split B (35%)Split C (40%)
2021Apr205XCompany A00151.2571.7582
2021Apr627YCompany A001156.75219.45250.8
2021Apr583ZCompany A001145.75204.05233.2
2021Apr647XCompany A002161.75226.45258.8
2021Apr954YCompany A002238.5333.9381.6
2021Apr153ZCompany A00238.2553.5561.2
2021May314XCompany A00378.5109.9125.6
2021May747YCompany A003186.75261.45298.8
2021May784ZCompany A003196274.4313.6
2021May907XCompany A004226.75317.45362.8
2021May879YCompany A004219.75307.65351.6
2021May865ZCompany A004216.25302.75346
2021Jun142XCompany A00535.549.756.8
2021Jun122YCompany A00530.542.748.8
2021Jun887ZCompany A005221.75310.45354.8
2021Jun144XCompany A0063650.457.6
2021Jun679YCompany A006169.75237.65271.6
2021Jun216ZCompany A0065475.686.4
2021Jul349XCompany A00787.25122.15139.6
2021Jul853YCompany A007213.25298.55341.2
2021Jul664ZCompany A007166232.4265.6
2021Jul172XCompany A0084360.268.8
2021Jul113YCompany A00828.2539.5545.2
2021Jul657ZCompany A008164.25229.95262.8
2021Aug239XCompany A00959.7583.6595.6
2021Aug216YCompany A0095475.686.4
2021Aug586ZCompany A009146.5205.1234.4
2021Aug770XCompany A010192.5269.5308
2021Aug777YCompany A010194.25271.95310.8
2021Aug738ZCompany A010184.5258.3295.2

Is there a particular reason you are creating these calculated columns? What are the 3 different tables for?

Normally you would use measures for these type of calculations. Calculated columns are static information (they are calculated and loaded with the model); measures are dynamic (and is the recommended way to work with numeric values) 

Are the splits the % of the amount?

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul, I hope below three tables can explain what exactly I am looking for. Because of the confidentiality of the data i am unable to share my actual data. First table is the data table, Second table is the Split Table, Third one is what I am looking for (It is basically same as your first post). Please help me out.

 

FiscalYearFiscalMonthNoAmountCostTypeInvoiceNo
2021Apr658Company A001
2021Apr458Company A001
2021Apr368Company A001
2021Apr430Company A001
2021Apr786Company B001
2021Apr574Company B001
2021Apr602Company B001
2021Apr634Company B001
2021Apr610Company C001
2021Apr268Company C001
2021Apr373Company C001
2021Apr366Company C001
2021Apr457Company D001
2021Apr369Company D001
2021Apr453Company E001
2021Apr217Company A002
2021Apr556Company A002
2021Apr731Company A002
2021Apr310Company A002
2021Apr893Company B002
2021Apr811Company B002
2021Apr591Company B002
2021Apr263Company B002
2021Apr749Company C002
2021Apr605Company C002
2021Apr943Company C002
2021Apr305Company C002
2021Apr218Company D002
2021Apr898Company D002
2021Apr148Company E002
2021May851Company A003
2021May807Company A003
2021May768Company A003
2021May376Company A003
2021May882Company B003
2021May591Company B003
2021May483Company B003
2021May690Company B003
2021May499Company C003
2021May539Company C003
2021May398Company C003
2021May649Company C003
2021May397Company D003
2021May243Company D003
2021May720Company E003
2021May624Company A004
2021May950Company A004
2021May197Company A004
2021May802Company A004
2021May612Company B004
2021May803Company B004
2021May594Company B004
2021May984Company B004
2021May748Company C004
2021May982Company C004
2021May488Company C004
2021May932Company C004
2021May967Company D004
2021May696Company D004
2021May547Company E

004

 

CostTypeAllocation
Cost Type A on Company A59%
Cost Type B on Company A14%
Cost Type C on Company A27%
Cost Type C on Company C27%
Cost Type C on Company D36%

 

  AprMay
  001002003004
Company ACost Type A    
 Cost Type B    
 Cost Type C    
 Cost Type D    
Sub Total     
Company BCost Type A    
 Cost Type B    
 Cost Type C    
 Cost Type D    
Sub Total     
Company CCost Type A    
 Cost Type B    
 Cost Type C    
 Cost Type D    
Sub Total     
Company DCost Type A    
 Cost Type B    
 Cost Type C    
 Cost Type D    
Sub Total     
Company ECost Type A    
 Cost Type B    
 Cost Type C    
 Cost Type D    
Sub Total     
Grand Total     

Thanks.  Can you please include the Cost Type Column in the data sample in the data table?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi Paul, I have not tried to replicate my data as much as possible. Hope this will work to help me out. Desired Result is coming from "Total Labor Table" from each of the calculated columns. Need same desired result from the other tables as well. Please let me know if this works. Thanks Again.

Data Table     
YearMonthAmountCompanyCostTypeInvoiceNo
2021Apr515CTotal Labor Cost001
2021Apr743BTotal Labor Cost001
2021Apr940DTotal Labor Cost001
2021Apr343ATotal Labor Cost001
2021Apr764CRaw Material Cost001
2021Apr913BRaw Material Cost001
2021Apr473DRaw Material Cost001
2021Apr243ARaw Material Cost001
2021Apr333CSpare Parts001
2021Apr212ASpare Parts001
2021Apr947EOther Cost001
2021Apr534CTotal Labor Cost002
2021Apr367BTotal Labor Cost002
2021Apr736DTotal Labor Cost002
2021Apr592ATotal Labor Cost002
2021Apr777CRaw Material Cost002
2021Apr248BRaw Material Cost002
2021Apr141DRaw Material Cost002
2021Apr540ARaw Material Cost002
2021Apr130CSpare Parts002
2021Apr658ASpare Parts002
2021Apr269EOther Cost002

 

Total Labor  Table     Calculated Columns
YearMonthAmountCompanyCostTypeInvoiceNoWorker Cost (25%)Management Cost (35%)Other Cost (40%)
2021Apr688CTotal Labor Cost001172240.8275.2
2021Apr150BTotal Labor Cost00137.552.560
2021Apr211DTotal Labor Cost00152.7573.8584.4
2021Apr428ATotal Labor Cost001107149.8171.2
2021Apr923CTotal Labor Cost002230.75323.05369.2
2021Apr729BTotal Labor Cost002182.25255.15291.6
2021Apr133DTotal Labor Cost00233.2546.5553.2
2021Apr879ATotal Labor Cost002219.75307.65351.6

 

Raw Material Cost Table     Calculated Columns
YearMonthAmountCompanyCostTypeInvoiceNoMaterial Cost (80%)Management Cost (20%)
2021Apr846CRaw Material Cost001676.8169.2
2021Apr845BRaw Material Cost001676169
2021Apr566DRaw Material Cost001452.8113.2
2021Apr450ARaw Material Cost00136090
2021Apr978CRaw Material Cost002782.4195.6
2021Apr874BRaw Material Cost002699.2174.8
2021Apr705DRaw Material Cost002564141
2021Apr265ARaw Material Cost00221253

 

Spare Parts Table     Calculated Column
YearMonthAmountCompanyCostTypeInvoiceNoManagement Cost (20%)
2021Apr424CSpare Parts00184.8
2021Apr696ASpare Parts001139.2
2021Apr574CSpare Parts002114.8
2021Apr287ASpare Parts00257.4

 

Other Cost Table     
YearMonthAmountCompanyCostTypeInvoiceNo
20211394EOther Cost001
20211961EOther Cost002

 

Desired Result   
Labor Cost Apr
Worker CostCompany001002
 A107219.75
 B37.5182.25
 C172230.75
 D52.7533.25
Sub Total 369.25666
Management CostCompany001002
 A149.8307.65
 B52.5255.15
 C240.8323.05
 D73.8546.55
Sub Total 516.95932.4
Other CostCompany001002
 A171.2351.6
 B60291.6
 C275.2369.2
 D84.453.2
Sub Total 590.81065.6
Grand Total 14772664

 

@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:

FinalTable.JPG

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

By cost typeBy cost type

 

By companyBy company

 

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Hi Paul, I think this is amazing and will work for my requirements. I am going through your file and modifying my actual data/file accordingly and will let you know if I have any difficulty. And I will except your's as accepted solution. Great Work Paul.

Hi Paul, I was not sure on how to use measure to get the required results. And Yes, % (Which are fixed (Does not Change) coming from another table) is a split of the amount. Also the % split is different for different companies and that's why i used calculated column approach after splitting the data in 3 different tables. If there is another better way then please elp me out here. I appreciate your help in this matter.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors