Unpivot and 'sum' issues

[PBInonpro]

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. 

[Anonymous · Accepted solution]

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.

[AllisonKennedy]

@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.

[PBInonpro]

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.