Forum Discussion

silverfox's avatar
silverfox
Regular Visitor
6 years ago
Solved

Matrix table calculation in education setting.

Hi I'm new to Power Bi and Im trying to use it in a Education setting.

 

Below is the screen shot ( number 2) of the matrix table I have and would like to calculate the difference between column 1 and 2.

The data comes from a MIS in what I would describeScreen Shot 1 (Data from MIS)Screen shot 2 (Matrix table) a list form (See screen shot1) Any help with a solution would great.

 

 

 

 
 
Need Help
Tips and Tricks

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi silverfox 

    You could create a calculated column like

    new column=[column1]-[column2],

    If it doesn't work for your scenario, please let me know the columns for "row","column" and "value" field of the matrix.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • silverfox's avatar
      silverfox
      Regular Visitor
      6 years ago

      Hi Maggie

      Thanks for the reply.

       

      I think because I'm pulling the data into BI a certain way it will not give the individual pivoted columns ( I'm right in thinking the matrix is really just an excel pivot table?)

       

      I have the matrix set up as follows:

      Student name is Rows and this is from a different data set.
      Basic Details is columns and this give the subject name Geography, Geography Estimate etc
      Value is result which then gives a GCSE grade.

       

      The data come in the format on the second image I posted.

      Hope this helps and thanks for you help.

      Craig

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi silverfox 

        My visual 2 is recommended based on my experience.

        Here are steps i make it:

        1. assume you table as below

        2.

        In Edit queries,

        Split column for "detail" column,

        replace the "null" with "Actual" value,

        then close&&apply, return to report view

         

        3.

        Create measures in Detail table

        Measure =
VAR actual =
    CALCULATE (
        SUM ( 'detail table'[Result] ),
        FILTER (
            ALLSELECTED ( 'detail table' ),
            'detail table'[student id] = MAX ( 'detail table'[student id] )
                && 'detail table'[Basic details.category] = "Actual"
        )
    )
VAR estimate =
    CALCULATE (
        SUM ( 'detail table'[Result] ),
        FILTER (
            ALLSELECTED ( 'detail table' ),
            'detail table'[student id] = MAX ( 'detail table'[student id] )
                && 'detail table'[Basic details.category] = "Estimate"
        )
    )
RETURN
    actual - estimate

Measure 2 = IF(ISINSCOPE('detail table'[Basic details.category]),SUM('detail table'[Result]),[Measure])

         

        4. edit the format of the matrix->Subtotal

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi silverfox 

    I can work wout with two kinds of visual, please let me know which you like better or anything else.

    1.

    2.

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.