Best way to shape data when they contain both yearly and monthly column

[ValeriaBreve]

Dear experts,

I have a set of data that combines monthly columns and yearly columns (see below).

I see 2 options to use this:

1) Split the table in 2: one that has the yearly attributes, one that contains the monthly figures (that I will unpivot), and then connect these 2 tables in the report via the "Product" column 

2) Keep the table as is, unpivot the monthly figures, which will make the yearly figures repeat on multiple rows for the same product, and then use the yearly figures as "Average" on aggregation (they will give incorrect results if summed because they are repeated on several rows)

What's the best way of handling this situation? 

Thanks!

Kind regards

Valeria

Product	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	2022 Forecast
A	87	60	68	28	21	68	36	10	43	77	21	34	700
B	44	59	85	28	31	11	55	66	3	30	87	30	500
C	62	8	31	32	33	69	62	33	29	50	76	12	900
D	74	85	57	8	85	6	62	79	90	58	100	81	1100
E	7	81	96	55	33	51	21	78	99	76	44	67	300

[ValeriaBreve]

Thank you so much! Great material 🙂

[amitchandak · Accepted solution]

@ValeriaBreve , Go with the first one, Split and have a year and monthly tables, and Unpivot both.

Now have date in both tables and join them with a common date table

Date = datevalues("01-"&[Month])

Date =date([Year],1,1) //or Date([Year],12,31)

then refer

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

refer if needed

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...