Forum Discussion
Urgent Support for matrix table
Dear all .. not very good with Power Bi and need your support to re create the above table in matrix table :
Bellow is may data :
| Year_Month | Product | Customer | Real PCS | Bdg PCS | Invoicing US$ | Invoice BDG US$ | Margin US$ | Margin BDG US$ |
| 10/1/2017 | Planes | TK MAXX | 2.500 | 3.254 | 17.500.000 | 19.351.538 | 12.500.000 | 16.367.620 |
| 10/1/2017 | Cars | PEPCO | 3.245 | 2.456 | 9.735.000 | 6.849.784 | 3.894.000 | 2.846.504 |
| 10/1/2017 | Yahts | AUCHAN | 1.267 | 1.100 | 7.602.000 | 6.422.900 | 3.420.900 | 3.131.700 |
| 10/1/2017 | Planes | TOYS R US | 4.567 | 3.497 | 31.969.000 | 20.796.659 | 22.835.000 | 17.589.910 |
| 11/1/2017 | Cars | TOYS R US | 9.808 | 9.707 | 29.424.000 | 27.072.823 | 11.769.600 | 11.250.413 |
| 11/1/2017 | Cars | AUCHAN | 54.443 | 50.000 | 163.329.000 | 139.450.000 | 65.331.600 | 57.950.000 |
| 11/1/2017 | Yahts | TOYS R US | 33.422 | 33.400 | 200.532.000 | 195.022.600 | 90.239.400 | 95.089.800 |
| 12/1/2017 | Cars | AUCHAN | 34.567 | 35.000 | 103.701.000 | 97.615.000 | 41.480.400 | 40.565.000 |
| 12/1/2017 | Yahts | TOYS R US | 55.656 | 55.050 | 333.936.000 | 321.436.950 | 150.271.200 | 156.727.350 |
| 12/1/2017 | Planes | PEPCO | 343.432 | 234.569 | 2.404.024.000 | 1.394.981.843 | 1.717.160.000 | 1.179.882.070 |
| 1/1/2018 | Planes | PEPCO | 3.453 | 4.535 | 24.171.000 | 26.969.645 | 17.265.000 | 22.811.050 |
| 1/1/2018 | Cars | TK MAXX | 32.432 | 20.000 | 97.296.000 | 55.780.000 | 38.918.400 | 23.180.000 |
| 1/1/2018 | Yahts | TK MAXX | 2.345 | 3.232 | 14.070.000 | 18.871.648 | 6.331.500 | 9.201.504 |
| 11/1/2017 | Planes | TOYS R US | 23.435 | 23.245 | 164.045.000 | 138.238.015 | 117.175.000 | 116.922.350 |
| 11/1/2017 | Planes | PEPCO | 34.234 | 35.000 | 239.638.000 | 208.145.000 | 171.170.000 | 176.050.000 |
| 10/1/2017 | Cars | TK MAXX | 43.435 | 44.000 | 130.305.000 | 122.716.000 | 52.122.000 | 50.996.000 |
| 11/1/2017 | Planes | AUCHAN | 432.323 | 440.000 | 3.026.261.000 | 2.616.680.000 | 2.161.615.000 | 2.213.200.000 |
| 11/1/2017 | Yahts | PEPCO | 233.434 | 230.000 | 1.400.604.000 | 1.342.970.000 | 630.271.800 | 654.810.000 |
| 11/1/2017 | Cars | TK MAXX | 2.243.432 | 2.340.000 | 6.730.296.000 | 6.526.260.000 | 2.692.118.400 | 2.712.060.000 |
| 12/1/2017 | Planes | TOYS R US | 24.342 | 24.452 | 170.394.000 | 145.416.044 | 121.710.000 | 122.993.560 |
| 11/1/2017 | Yahts | TK MAXX | 242 | 200 | 1.452.000 | 1.167.800 | 653.400 | 569.400 |
| 12/1/2017 | Planes | PEPCO | 4.224 | 4.000 | 29.568.000 | 23.788.000 | 21.120.000 | 20.120.000 |
| 12/1/2017 | Planes | TK MAXX | 2.342.423 | 2.345.000 | 16.396.961.000 | 13.945.715.000 | 11.712.115.000 | 11.795.350.000 |
| 12/1/2017 | Cars | TOYS R US | 34.242 | 33.000 | 102.726.000 | 92.037.000 | 41.090.400 | 38.247.000 |
| 12/1/2017 | Cars | PEPCO | 24.233 | 23.230 | 72.699.000 | 64.788.470 | 29.079.600 | 26.923.570 |
| 1/1/2018 | Cars | AUCHAN | 24.324 | 32.324 | 72.972.000 | 90.151.636 | 29.188.800 | 37.463.516 |
| 12/1/2017 | Yahts | TOYS R US | 54.454 | 55.000 | 326.724.000 | 321.145.000 | 147.025.800 | 156.585.000 |
| 12/1/2017 | Planes | TK MAXX | 53.453 | 47.000 | 374.171.000 | 279.509.000 | 267.265.000 | 236.410.000 |
| 1/1/2018 | Planes | TOYS R US | 324 | 400 | 2.268.000 | 2.378.800 | 1.620.000 | 2.012.000 |
| 1/1/2018 | Yahts | TK MAXX | 235.235 | 230.000 | 1.411.410.000 | 1.342.970.000 | 5.005 | 654.810.000 |
| 1/1/2018 | Cars | TOYS R US | 2.342 | 2.500 | 7.026.000 | 6.972.500 | 2.810.400 | 2.897.500 |
| 1/1/2018 | Planes | TK MAXX | 2.354 | 2.400 | 16.478.000 | 14.272.800 | 11.770.000 | 12.072.000 |
| 1/1/2018 | Yahts | AUCHAN | 234 | 345 | 1.404.000 | 2.014.455 | 631.800 | 982.215 |
| 1/1/2018 | Cars | TOYS R US | 2.344 | 2.324 | 7.032.000 | 6.481.636 | 2.812.800 | 2.693.516 |
-For Price US$/Pcs create a measure ? Sum(Invoicing US$)/Sum(Real Pcs) the same for BDG
sum(Invoice BDG US$)/sum(Bdg Pcs).
Delta new column + Real US$/Pcs-BDG Pcs
Same for Gross MArgin:
-Weight of Margin on invoicing :
Measure: MArgin US$/Invoicing US$ - same for BDg and Delta to be difference.
-TO show in Table Th Pcs - Do we have to create a new column with new calculation ? Real Pcs/1000?
Mill USD Table Th Pcs - Do we have to create a new column with new calculation ? Invoicing US$/1000000.
The Visualisation on dashbord is important to show the same info as shown.
Please Help
Many Thanks
Hi alexbalazsalex,
You can do the grouping base on the column in two ways:
1. Advance Query Editor
- Add conditional column and place the following code:
2. In front end
- Select the column you and and right click or ... and choose New group
- On the next window select the value to group and choose the names
- Then add this to the columns in the matrix.
In attach is the PBIX file for download (this is a WeTransfer and it's only available 7 days)
Any further questions please tell me.
And sorry for the delay.
Regards,
MFelix
14 Replies
Hi alexbalazsalex,
You should create the measure has you describe howver if you want to have the values by 1000 you should also create the measure for the invoicing values and the quantities not columns.
This way you can use those measure to calculate other measures so you would have something like this:
Invoicing USD = SUM('Fact'[Invoicing US$])/1000000 Budget USD = SUM('Fact'[Invoice BDG US$])/1000000 Make the same for pieces Then use it on the other measures like: Invoicing vs Budget = [Invoicing USD]-[Budget USD]Then just add to your matrix visual in the values
Regards,
MFelix
Hi Felix
thanks for the reply ,
any ideas how i should add in the matrix the columns,
to show
Metrics like Invoicing USD Mill and then under real vs Bdg.
Volume Th Pcs real than real vs BDg ... this is my main doubt
