topic Re: Need Help with Matrix Visual - Variance calc in DAX Commands and Tips

Need Help with Matrix Visual - Variance calc

komaragiri — Fri, 30 May 2025 01:48:10 GMT

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.

Re: Need Help with Matrix Visual - Variance calc

SamsonTruong — Fri, 30 May 2025 02:13:13 GMT

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

Re: Need Help with Matrix Visual - Variance calc

komaragiri — Fri, 30 May 2025 02:20:50 GMT

SamsonTruong Thanks for your suggestion. Worked. Kudos to you for helping

Re: Need Help with Matrix Visual - Variance calc

SamsonTruong — Fri, 30 May 2025 03:21:57 GMT

komaragiri Happy to hear the solution worked!