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
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.