Forum Discussion
Unpivot and 'sum' issues
I have a data source that has 12 columns for the year (Jan - Dec). The only way I see to run the model correctly is to unpivot the 12 columns and assign Jan - Dec a value of 1-12 (or rename and change to date format).
The issue is this, when I unpivot the columns the total sum is now 12 times the value it should be; do I need to make 12 different measures to account for each month for the sum or is there a DAX measure that can sum based on month.
Example.
| Jan | Feb | Mar |
| 5 | 10 | 15 |
| 30 | 25 | 20 |
| 15 | 0 | 5 |
When I unpivot I get the following:
| Jan | 5 |
| Jan | 30 |
| Jan | 15 |
| Feb | 10 |
| Feb | 25 |
| Feb | 0 |
| Mar | 15 |
| Mar | 20 |
| Mar | 5 |
Do I need to make a measure for each month? or is there a solution to only have one measure? The goal is forecast vs actual finance data.
Hi PBInonpro ,
You could use group by to sum them up.
After unpivotted, you get this.
Right-click the Attribute column, select Group By...
Set up as follows and then click OK.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
PBInonpro Why is the total sum 10x what it should be? You can still slice by month, ideally using a dimDate table https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
You're definitely on the right track with the unpivot.
Thanks, I think this is the solution but is there a way to visualize this? I am not quite 100% grasping why the DIMDATE matters.
Hi PBInonpro ,
You could use group by to sum them up.
After unpivotted, you get this.
Right-click the Attribute column, select Group By...
Set up as follows and then click OK.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
