Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |