I am fairly new to powerbi and I apprecaite any help.
I have table A, showing the following information, which shows by product the total quantity available, which is then split by area.
Product, Total quantity, Area A, AreaB, AreaC
ABC, 100, 20, 30, 50
CDA, 50, 0, 20, 30
CDB, 25, 15, 5, 5
Product, qty sold, Area
ABC, 5, Area A
ABC, 0, Area B
ABC, 30, Area C
CDA, 15, Area B
CDB, 10, Area A
CDB, 3, Area B
CDB, 5, Area C
I want to be able to calculate the % of products sold by product and area. How can I turn table B as below in order to create these calculations?
Product, Area, Total quantity, qty sold
ABC, Area A, 20, 5
ABC, Area B, 20,0
ABC, Area C, 50,30
CDA, Area A, 0,0
CDA, Area B, 20, 15
CDA, Area C, 30,0
CDB, Area A, 15, 10
CDB, Area B, 5, 3
CDB, Area C, 5, 5
thank you so much for your help
Hi @ninakarsa ,
If you unpivot Table A. Rename the columns as you want. Attribute to 'Area' and Value to 'Area Total QTY'
you can then do a Merge Queries as new with the Product and Area columns as keys between the two tables.
This will result in a Table C with the columns Product, Total quantity, Area, Area Total QTY and a column called 'Table B'
Then you only have to expand the column (click on the symbol with the left and right arrows) and only select the column 'qty sold' and you you the table you requested.
hope this helps
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.