Matrix table calculation in education setting.

[silverfox]

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.

[v-juanli-msft · Accepted solution]

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.

[v-juanli-msft · Accepted solution]

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.

[v-juanli-msft]

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]

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]

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]

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

[v-juanli-msft · Accepted solution]

Hi @silverfox 

See details here:

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

Best Regards
Maggie