Forum Discussion
Adding Custom calculated Columns to Table
Hello everyone,
I'm trying to create a table as shown below, I have regions and sales, I want to group sales by months as M1: January...
and then add another column to calculate the variance between latest month and 2 previous months.
The thing that I'm struggling with is adding new calculated columns such as the average of the latest 2 months and variance (I am using a matrix table but then I can't add the latest month and variance)
Appreciate any help or any resources on creating complex custom tables.
| Region | M12 | M1 | M2 | M3 | Latest full month | Avg. Of Latest 2 Months | Variance | Prior Month | Variance |
| Region 1 | 44 | 23 | 43 | 44 | 43 | 34 | 28% | 23 | 87% |
| Region 2 | 44 | 23 | 34 | 54 | 34 | 34 | 1% | 23 | 48% |
| Region 3 | 33 | 43 | 34 | 34 | 34 | 38 | -11% | 43 | -21% |
| Region 4 | 22 | 43 | 4 | 43 | 4 | 33 | -88% | 43 | -91% |
| Region 5 | 33 | 45 | 32 | 33 | 32 | 39 | -18% | 45 | -29% |
| Total | 176 | 177 | 147 | 208 | 147 | 177 | -17% | 177 | -17% |
Hi Anonymous ,
For calculating variance in Power BI, we can use DAX function such as VAR.S or VAR.P.
VAR.S function (DAX) - DAX | Microsoft Learn
VAR.P function (DAX) - DAX | Microsoft LearnFor calcualting the average of the latest 2 months, we can also use DAX formula to achieve it. I found some similar posts for your reference.
Solved: SOLVED.DAX Measure previous 3 months average - Microsoft Fabric Community
Solved: Average of 3 Months - Microsoft Fabric Community
Solved: dynamically calculate average for last 3 months sa... - Microsoft Fabric CommunityBest 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
Hi Anonymous ,
Unfortunately, you cannot add a single column in the matrix visual which is by design.
If you add a field to the Values of the matrix, it will only add one column of values for each month, as shown below:
It's recommended that you try adding these columns with a table visual.
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.
Hi Anonymous ,
For calculating variance in Power BI, we can use DAX function such as VAR.S or VAR.P.
VAR.S function (DAX) - DAX | Microsoft Learn
VAR.P function (DAX) - DAX | Microsoft LearnFor calcualting the average of the latest 2 months, we can also use DAX formula to achieve it. I found some similar posts for your reference.
Solved: SOLVED.DAX Measure previous 3 months average - Microsoft Fabric Community
Solved: Average of 3 Months - Microsoft Fabric Community
Solved: dynamically calculate average for last 3 months sa... - Microsoft Fabric CommunityBest Regards
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.