Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
@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
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 |
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
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.
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.
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 |
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?
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.
FiscalYear | FiscalMonthNo | Amount | CostType | InvoiceNo |
2021 | Apr | 658 | Company A | 001 |
2021 | Apr | 458 | Company A | 001 |
2021 | Apr | 368 | Company A | 001 |
2021 | Apr | 430 | Company A | 001 |
2021 | Apr | 786 | Company B | 001 |
2021 | Apr | 574 | Company B | 001 |
2021 | Apr | 602 | Company B | 001 |
2021 | Apr | 634 | Company B | 001 |
2021 | Apr | 610 | Company C | 001 |
2021 | Apr | 268 | Company C | 001 |
2021 | Apr | 373 | Company C | 001 |
2021 | Apr | 366 | Company C | 001 |
2021 | Apr | 457 | Company D | 001 |
2021 | Apr | 369 | Company D | 001 |
2021 | Apr | 453 | Company E | 001 |
2021 | Apr | 217 | Company A | 002 |
2021 | Apr | 556 | Company A | 002 |
2021 | Apr | 731 | Company A | 002 |
2021 | Apr | 310 | Company A | 002 |
2021 | Apr | 893 | Company B | 002 |
2021 | Apr | 811 | Company B | 002 |
2021 | Apr | 591 | Company B | 002 |
2021 | Apr | 263 | Company B | 002 |
2021 | Apr | 749 | Company C | 002 |
2021 | Apr | 605 | Company C | 002 |
2021 | Apr | 943 | Company C | 002 |
2021 | Apr | 305 | Company C | 002 |
2021 | Apr | 218 | Company D | 002 |
2021 | Apr | 898 | Company D | 002 |
2021 | Apr | 148 | Company E | 002 |
2021 | May | 851 | Company A | 003 |
2021 | May | 807 | Company A | 003 |
2021 | May | 768 | Company A | 003 |
2021 | May | 376 | Company A | 003 |
2021 | May | 882 | Company B | 003 |
2021 | May | 591 | Company B | 003 |
2021 | May | 483 | Company B | 003 |
2021 | May | 690 | Company B | 003 |
2021 | May | 499 | Company C | 003 |
2021 | May | 539 | Company C | 003 |
2021 | May | 398 | Company C | 003 |
2021 | May | 649 | Company C | 003 |
2021 | May | 397 | Company D | 003 |
2021 | May | 243 | Company D | 003 |
2021 | May | 720 | Company E | 003 |
2021 | May | 624 | Company A | 004 |
2021 | May | 950 | Company A | 004 |
2021 | May | 197 | Company A | 004 |
2021 | May | 802 | Company A | 004 |
2021 | May | 612 | Company B | 004 |
2021 | May | 803 | Company B | 004 |
2021 | May | 594 | Company B | 004 |
2021 | May | 984 | Company B | 004 |
2021 | May | 748 | Company C | 004 |
2021 | May | 982 | Company C | 004 |
2021 | May | 488 | Company C | 004 |
2021 | May | 932 | Company C | 004 |
2021 | May | 967 | Company D | 004 |
2021 | May | 696 | Company D | 004 |
2021 | May | 547 | Company E | 004 |
CostType | Allocation |
Cost Type A on Company A | 59% |
Cost Type B on Company A | 14% |
Cost Type C on Company A | 27% |
Cost Type C on Company C | 27% |
Cost Type C on Company D | 36% |
Apr | May | ||||
001 | 002 | 003 | 004 | ||
Company A | Cost Type A | ||||
Cost Type B | |||||
Cost Type C | |||||
Cost Type D | |||||
Sub Total | |||||
Company B | Cost Type A | ||||
Cost Type B | |||||
Cost Type C | |||||
Cost Type D | |||||
Sub Total | |||||
Company C | Cost Type A | ||||
Cost Type B | |||||
Cost Type C | |||||
Cost Type D | |||||
Sub Total | |||||
Company D | Cost Type A | ||||
Cost Type B | |||||
Cost Type C | |||||
Cost Type D | |||||
Sub Total | |||||
Company E | Cost 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?
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 | |||||
Year | Month | Amount | Company | CostType | InvoiceNo |
2021 | Apr | 515 | C | Total Labor Cost | 001 |
2021 | Apr | 743 | B | Total Labor Cost | 001 |
2021 | Apr | 940 | D | Total Labor Cost | 001 |
2021 | Apr | 343 | A | Total Labor Cost | 001 |
2021 | Apr | 764 | C | Raw Material Cost | 001 |
2021 | Apr | 913 | B | Raw Material Cost | 001 |
2021 | Apr | 473 | D | Raw Material Cost | 001 |
2021 | Apr | 243 | A | Raw Material Cost | 001 |
2021 | Apr | 333 | C | Spare Parts | 001 |
2021 | Apr | 212 | A | Spare Parts | 001 |
2021 | Apr | 947 | E | Other Cost | 001 |
2021 | Apr | 534 | C | Total Labor Cost | 002 |
2021 | Apr | 367 | B | Total Labor Cost | 002 |
2021 | Apr | 736 | D | Total Labor Cost | 002 |
2021 | Apr | 592 | A | Total Labor Cost | 002 |
2021 | Apr | 777 | C | Raw Material Cost | 002 |
2021 | Apr | 248 | B | Raw Material Cost | 002 |
2021 | Apr | 141 | D | Raw Material Cost | 002 |
2021 | Apr | 540 | A | Raw Material Cost | 002 |
2021 | Apr | 130 | C | Spare Parts | 002 |
2021 | Apr | 658 | A | Spare Parts | 002 |
2021 | Apr | 269 | E | Other Cost | 002 |
Total Labor Table | Calculated Columns | |||||||
Year | Month | Amount | Company | CostType | InvoiceNo | Worker Cost (25%) | Management Cost (35%) | Other Cost (40%) |
2021 | Apr | 688 | C | Total Labor Cost | 001 | 172 | 240.8 | 275.2 |
2021 | Apr | 150 | B | Total Labor Cost | 001 | 37.5 | 52.5 | 60 |
2021 | Apr | 211 | D | Total Labor Cost | 001 | 52.75 | 73.85 | 84.4 |
2021 | Apr | 428 | A | Total Labor Cost | 001 | 107 | 149.8 | 171.2 |
2021 | Apr | 923 | C | Total Labor Cost | 002 | 230.75 | 323.05 | 369.2 |
2021 | Apr | 729 | B | Total Labor Cost | 002 | 182.25 | 255.15 | 291.6 |
2021 | Apr | 133 | D | Total Labor Cost | 002 | 33.25 | 46.55 | 53.2 |
2021 | Apr | 879 | A | Total Labor Cost | 002 | 219.75 | 307.65 | 351.6 |
Raw Material Cost Table | Calculated Columns | ||||||
Year | Month | Amount | Company | CostType | InvoiceNo | Material Cost (80%) | Management Cost (20%) |
2021 | Apr | 846 | C | Raw Material Cost | 001 | 676.8 | 169.2 |
2021 | Apr | 845 | B | Raw Material Cost | 001 | 676 | 169 |
2021 | Apr | 566 | D | Raw Material Cost | 001 | 452.8 | 113.2 |
2021 | Apr | 450 | A | Raw Material Cost | 001 | 360 | 90 |
2021 | Apr | 978 | C | Raw Material Cost | 002 | 782.4 | 195.6 |
2021 | Apr | 874 | B | Raw Material Cost | 002 | 699.2 | 174.8 |
2021 | Apr | 705 | D | Raw Material Cost | 002 | 564 | 141 |
2021 | Apr | 265 | A | Raw Material Cost | 002 | 212 | 53 |
Spare Parts Table | Calculated Column | |||||
Year | Month | Amount | Company | CostType | InvoiceNo | Management Cost (20%) |
2021 | Apr | 424 | C | Spare Parts | 001 | 84.8 |
2021 | Apr | 696 | A | Spare Parts | 001 | 139.2 |
2021 | Apr | 574 | C | Spare Parts | 002 | 114.8 |
2021 | Apr | 287 | A | Spare Parts | 002 | 57.4 |
Other Cost Table | |||||
Year | Month | Amount | Company | CostType | InvoiceNo |
2021 | 1 | 394 | E | Other Cost | 001 |
2021 | 1 | 961 | E | Other Cost | 002 |
Desired Result | |||
Labor Cost | Apr | ||
Worker Cost | Company | 001 | 002 |
A | 107 | 219.75 | |
B | 37.5 | 182.25 | |
C | 172 | 230.75 | |
D | 52.75 | 33.25 | |
Sub Total | 369.25 | 666 | |
Management Cost | Company | 001 | 002 |
A | 149.8 | 307.65 | |
B | 52.5 | 255.15 | |
C | 240.8 | 323.05 | |
D | 73.85 | 46.55 | |
Sub Total | 516.95 | 932.4 | |
Other Cost | Company | 001 | 002 |
A | 171.2 | 351.6 | |
B | 60 | 291.6 | |
C | 275.2 | 369.2 | |
D | 84.4 | 53.2 | |
Sub Total | 590.8 | 1065.6 | |
Grand Total | 1477 | 2664 |
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:
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |