Adding Custom calculated Columns to Table

[Anonymous]

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%

[Anonymous]

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.

[Anonymous]

Hi Stephen,

thanks for the reply, I'm new to Power BI, I've used Qlik Sense which was very easy to add ad-hoc columns to the table but that's not the case with Power BI, do you have any resources to share on how to do Table Visual?

[Anonymous · Accepted solution]

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 Learn

For 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 Community

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.