Forum Discussion

komaragiri's avatar
komaragiri
Frequent Visitor
1 year ago
Solved

Need Help with Matrix Visual - Variance calc

Here is my data file

Resource IdResource NameworkdateHoursflagdata source
12345John Doeelectrical1/1/202510Buildingactual
12345John Doeelectrical1/1/202520Buildingplanned
12345John Doeelectrical1/1/202530Roomactual
12345John Doeelectrical1/1/202540Roomplanned

 

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

  • 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