Forum Discussion
Two column with grouped rows
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
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
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.