Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |
Solved! Go to Solution.
Hi @hadiana ,
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.
Hi @hadiana ,
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 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?
Hi @hadiana ,
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |