Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 describe a list form (See screen shot1) Any help with a solution would great.
Solved! Go to 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.
Hi @silverfox
See details here:
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting
Best Regards
Maggie
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.
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.
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
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.
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
Hi @silverfox
See details here:
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting
Best Regards
Maggie
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |