Data modeling

Rgrullon — Fri, 28 Jul 2023 13:12:40 GMT

Hi Guys, in new in the power bi world, and I`m receiving some basic information on Excel, I'm trying to design a Kpi master to get the best data for a small company. I got stuck in some things for example I know how to merge and combine columns but, now I want to match columns with rows and have this data like xlookup from Excel. I will let example data to better know the problem.

This is the Main data of sales I get, as you can see the products are columns and stores are rows

also, we get the same store but different weeks, so I design and unique id using =concat( week+ store)

STORE	WEEK	id	Mission - Salsa - Medium - Chunky - 16.0oz (7373100954)	Calidad - Chip - White - Triangle - Cello - 11.0oz (7794800907)	Calidad - Chip - Yellow - Triangle - Cello - 11.0oz (7794800906)	Great Value - Flour - Soft Taco - Carb Balance - 8.0 - 8ct - 12.0oz (7874229483)	Guerrero - Corn - White - 5.5 - 30ct - 25.0oz (4856406002)	Guerrero - Corn - White - 5.5 - 80ct - 66.67oz (4856406000)	Guerrero - Flour - Burrito - Casera - 9.5 - 8ct - 20.0oz (4856407205)
PLAZA TROPICAL 167426	Wk 25 Jun/23 (18-24)	Wk 25 Jun/23 (18-24)PLAZA TROPICAL 167426	189	189	11	11	11	11	8
M1ISSION ACCOMPLISHED LLC 983407	Wk 24 Jun/23 (11-17)	Wk 24 Jun/23 (11-17)MISSION ACCOMPLISHED LLC 983407	187	187	1	-9	-4	-13	11
MISSION ACCOMPLISHED LLC 983416	Wk 22 Jun/23 (28-3)	Wk 22 Jun/23 (28-3)MISSION ACCOMPLISHED LLC 983416	37	37	11	0	-17	65	11

Now imagine I get this data that shows me the code of each product and also the total sales and the AWS (average week sales)

and I want to match the items on this with the items on the upper table and extract: code, mdq(minimum delivery qty), total sales, and aws.

code	description	MDQ	TOTAL SALES	AWS
3661	Mission - Salsa - Medium - Chunky - 16.0oz (7373100954)	12	1500	214.29
7751	Calidad - Chip - White - Triangle - Cello - 11.0oz (7794800907)	16	1600	228.57
7752	Great Value - Flour - Soft Taco - Carb Balance - 8.0 - 8ct - 12.0oz (7874229483)	16	1700	242.57

also, this other table has the store on columns and the name and route.

Sold-to	NameRegion
PLAZA TROPICAL 167426	826 W.Orlando-Daniel
071 SPR TRG #1519 113392	G657/698 N.Clermont-Gustavo
M1ISSION ACCOMPLISHED LLC 983407	412 Oviedo-Jefferson
MISSION ACCOMPLISHED LLC 983416	414 Poinciana-Carlos
071 SPR TRG #2264 132358	G803/408 Winter Garden-Michelet

now my problem is to get just one single table that has all those information by store so I can make charts and present just one complete report, of course, I get thousands of rows and columns of information but with this example data I think you guys can help me also I add some images attached.

lastly, this is how my dashboard is looking now, its just a dummy version of course

topic Data modeling in Report Server

Data modeling