New Measure to pick up a value in another table

[GCC]

Hello,

I'm a newby to Power BI and Dax and struggling with what is probably a very simple piece of code.

I have two tables: FactSQA and DimRolls joined by a one to many relationship on 'School_Key'.

The FactSQA table contains the SQA awards (1, 2 or 3) for each pupil at each school.

The DimRolls table contains the rolls for the S4 classes in each school.

I've created a measure which counts the number of 1 & 2 awards for each subject (English & Maths) within each school.

I now need to take this number and divide it by the class roll held in the DimRolls table, then times by 100 to obtain the percentage. Obtaining the class rolls is causing me difficulties. Can anyone help?

Table: FactSQA
RefNo	School	School_Code	Diet_Year	School_Key	Pupil_Code	Award	Subject	Stage
1	School 1	M001	2019	M001_2019	Pupil_001	1	English	S4
2	School 1	M001	2019	M001_2019	Pupil_002	1	Maths	S4
3	School 1	M001	2019	M001_2019	Pupil_003	2	English	S4
4	School 1	M001	2019	M001_2019	Pupil_004	2	English	S4
5	School 1	M001	2019	M001_2019	Pupil_005	3	Maths	S4
6	School 2	M002	2019	M002_2019	Pupil_006	1	English	S4
7	School 2	M002	2019	M002_2019	Pupil_007	2	English	S4
8	School 2	M002	2019	M002_2019	Pupil_008	2	Maths	S4
9	School 2	M002	2019	M002_2019	Pupil_009	3	English	S4
10	School 2	M002	2019	M002_2019	Pupil_010	3	English	S4

Table: DimRolls
School_Key	School_Code	Year	S4_Roll
M001_2019	M001	2019	10
M002_2019	M002	2019	12

Relationship: 1 to * on School_Key

School 1	Graded_Entries
Subject	Awards 1 & 2	% of S4 Roll
English	3	30
Maths	2	20

School 2	Graded_Entries
Subject	Awards 1 & 2	% of S4 Roll
English	4	33.3
Maths	1	8.3

Measure:	Awards 1 and 2 = Calculate(Count(FactSQA[Pupil_Code]),(FactSQA[Award]=1)||(FactSQA[Award]=2))
Measure Required:	(Awards 1 and 2) / S4_Roll *100

It's the '% of S4' which I'm trying to calculate.

Thanks in advance.

[GCC]

Hi Chen,

Many thanks for such a quick response.

When I use SUM in my code it sums the S4 roll for both schools (12 + 10) and therefore calculates the wrong percentage figure. How best should I tweak the code so that it only picks up a single school roll?

Regards

Geoff

[Anonymous]

Hi Geoff,

You are welcome 😉 ,

actually it depends on how will you show the result, 

If you want to calculate the result separately,

you could write two measure for each school,

%S4_School1 = CALCULATE(DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]));FactSQA[School_Code]="M001")

%S4_School2 = CALCULATE(DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]));FactSQA[School_Code]="M002")

Hope this would be helpful,

Regard,

Chen

[GCC]

Hi Chen,

I have a slicer at the top of the page so that the user can select their school. Therefore I need the code to work on all schools. 

When I try and use your new code, I'm prevented from entering FactSQA[School_Code]=DimRolls[School_Code].

Do you have any further ideas?

Regards

Geoff

[Anonymous]

Hi Geoff,

If you have already a slicer of School, the first code shold be working.

%S4 = DIVIDE( FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]))

Can I ask why do you need use FactSQA[School_Code]=DimRolls[School_Code] ? 😉

Chen

[GCC]

Hi Chen,

The first code you gave me is summing the S4 roll for all schools and not the single school selected in the slicer.

Regards

Geoff

[GCC · Accepted solution]

Hi Chen,

I had a year slicer on my report which was inpacting adverseley on the output.

Your code is working just fine.

Many thanks for your help.

Regards

Geoff

[Anonymous]

Hi there,

To calculate the %S4_Roll, you can simply write :

%S4 = DIVIDE(FactSQA[Awards 1 and 2];SUM(DimRolls[S4_Roll]))

Then you can get the %, and present them in a table.

Hope this is what you want!

Have a nice day,

Chen