Forum Discussion
Need Help with Matrix Visual - Variance calc
Here is my data file
| Resource Id | Resource Name | work | date | Hours | flag | data source |
| 12345 | John Doe | electrical | 1/1/2025 | 10 | Building | actual |
| 12345 | John Doe | electrical | 1/1/2025 | 20 | Building | planned |
| 12345 | John Doe | electrical | 1/1/2025 | 30 | Room | actual |
| 12345 | John Doe | electrical | 1/1/2025 | 40 | Room | planned |
I would like to get the report in this format ...using Matrix Visual. Need help with calculating the Variance as shown below.
Hi komaragiri ,
To achieve this matrix, we will create 3 measures. I've replicated your data in my environment.
First we will create a measure for Planned using the following DAX:Planned = CALCULATE( SUM(TestData[Hours]), TestData[data source] = "planned" )
Then we will create a measure for Actual using the following DAX:Actual = CALCULATE( SUM(TestData[Hours]), TestData[data source] = "actual" )
Finally we will create a third measure that calculates the variance between them using the following DAX:Variance = [Planned] - [Actual]
Once the meaures are created, we can leverage them in a Matrix like so:If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
3 Replies
- SamsonTruongSuper User
Hi komaragiri ,
To achieve this matrix, we will create 3 measures. I've replicated your data in my environment.
First we will create a measure for Planned using the following DAX:Planned = CALCULATE( SUM(TestData[Hours]), TestData[data source] = "planned" )
Then we will create a measure for Actual using the following DAX:Actual = CALCULATE( SUM(TestData[Hours]), TestData[data source] = "actual" )
Finally we will create a third measure that calculates the variance between them using the following DAX:Variance = [Planned] - [Actual]
Once the meaures are created, we can leverage them in a Matrix like so:If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.
Thanks,Samson
- komaragiriFrequent Visitor
SamsonTruong Thanks for your suggestion. Worked. Kudos to you for helping
- SamsonTruongSuper User
komaragiri Happy to hear the solution worked!