Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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...
Thank you so much! Great material 🙂
@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...
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |