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 ...
Need Help
Tips and Tricks
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.

      • silverfox's avatar
        silverfox
        Regular Visitor
        6 years ago

        Hi Maggie

         

        Your first version is perfect as a single page is just 1 subject. Is there way to conditionally format the difference column.

         

        Many thanks again

         

        Craig