Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @alexbalazsalex,
You can do the grouping base on the column in two ways:
1. Advance Query Editor
2. In front end
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Hi @alexbalazsalex,
In PBI it's possible to make an hierarchy between columns so the visual look you need would appear as you want with a first aggregated level and then a second level with the sub-value, however this is only possible on columns levels so you can create different hierarchy so in the matrix visuals those would look like you need to have them.
At the measure level that is not possible so if you add a measure to your data it will be repeated at all the levels of your matrix.
A workaround can be:
PCS vs Budget = CALCULATE(SUM(Data_Table[Value]); Data_Table[Type] = "Real PCS") - CALCULATE(SUM(Data_Table[Value]); Data_Table[Type] = "Bdg PCS")
This is just an high level explanation sorry for not getting this into detail will try to have a complete guide during this evening with full step by step with images.
This will change the way you calculate you measures since the information is on a different format than you have.
Hope this helps.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Starting to look good i think.. can you share the file with me so i can check the if formula you used and others ?
Many Thanks
Alex
Hi @alexbalazsalex,
You can do the grouping base on the column in two ways:
1. Advance Query Editor
2. In front end
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
i checked your option ... but i ve seen that you multiplied each row by type for each date ... in my case in a db of 100k rows ... it would be difficult..
Do you think there is other way around it ? or just this way ?
Many Thanks.
Alex
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
thanks for your patiente and suport ..
And this unpivoting can be done stragit in Power bi ? or have to be done before as i ve seen tha tyou have "Type" column in you attached fiel already ..
Thanks Alex
Hi @alexbalazsalex,
This unpivoting is done in the query editor.
If you go to the PBIX file I shared you can check how it works.
Any questions please tell me
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsDear All,
Could you please advise ? Getting nowhere unfortunalty ...
Thanks
Up
Hi @alexbalazsalex,
Today I'm dealing with some urgent work related issues. Will get into this later this evening and will try to provide an answer ASAP.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |