Combining multiple if statements with sum and subtract

[blessedberry]

Dear community,

I am relatively new to Power BI and I am trying to create a measure that will help me calculate a national student score based on the high school level recommendation they were given in elementary school.

High school level recommendation in our country (Netherlands) is ranked as follows (starting from low to high). There can also be a mixed advice (see 3, 5, 7, 9). I have already made an index column with this exact numbering. 

1. pro
2. vmbo b
3. vmbo b / vmbo k
4. vmbo k
5. vmbo k / vmbo (g)t
6. vmbo (g)t
7. vmbo (g)t / havo
8. Havo
9. Havo / vwo
10. Vwo 

The national student score is calculated using the students current high school level in year number 3 by the following conditions. 

1. If the student is currently in a lower high school level than was recommended at elementary school, the score is -1,0. 
2. If the student has a mixed recommendation and achieves the lowest of the two, the score is 0,0.
3. If the student has a mixed recommendation and achieves the highest of the two, the score is 1,0.
4. If a student is currently in a higher high school level than was recommended at elementary school, the score is +1,0.
5. If the student has havo recommendation and is currently at havo/vwo level, the score is +0,5.

Below is an example of an imaginary school, in the first column are the recommendations, and in the 2nd to 7th column are the number of students that achieved the corresponding high school level.

I am trying to achieve the following as the national student score is dependant on the recommendation that is given.

IF recommendation = 1(e.g. pro), then the sum of every table (since it is a higher level) is added. 

IF recommendation = 2(e.g. vmbo b), then the sum of vmbo k, vmbo (g)t, havo, havo/vwo and vwo is added since the count of vmbo b is neutral.

IF recommendation = 3, then the sum of vmbo k, vmbo (g)t, havo, havo/vwo and vwo is added since the count of vmbo b is neutral.

IF recommendation = 4, then the sum of vmbo (g)t, havo, havo/vwo and vwo is added since the count of vmbo k is neutral. This sum is then subtracted by the count of vmbo b, because this is a lower level. 

and so on.

I want to write a specific measure that takes into account all these calculations per row. The national student score column should then contain a specific score per recommendation. See column 8 for the desired result. 

Recommendation	vmbo b	vmbo k	vmbo (g)t	havo	havo/vwo	vwo	National student score (desired result)
vmbo k	0	0	1	0	0	0	1
vmbo k / (g)t	0	0	2	0	0	0	2
vmbo (g)t / havo	0	0	79	19	0	1	20
havo	0	0	27	107	0	23	-4
havo/vwo	0	0	2	2	0	1	-1
vwo	0	0	1	16	0	139	-17

I am very much looking forward to any tips that can guide me in the right direction.

Many thanks,

S

[Anonymous · Accepted solution]

Hi @blessedberry,

You can create a variable table in formula with all recommendation and level mapping and use it to get the current level, then you can write expression with if statement based on current level to calculate across the field.

formula =
VAR currentR =
    SELECTEDVALUE ( Table[Recommendation] )
VAR dictR =
    UNION (
        SELECTCOLUMNS ( { "pro" }, "Recommendation", [Value], "Level", 1 ),
        SELECTCOLUMNS ( { "vmbo b" }, "Recommendation", [Value], "Level", 2 ),
        SELECTCOLUMNS ( { "vmbo k" }, "Recommendation", [Value], "Level", 3 ),
        SELECTCOLUMNS ( { "vmbo g" }, "Recommendation", [Value], "Level", 4 )
    )
VAR currLevel =
    MAXX ( FILTER ( dictR, [Recommendation] = currentR ), [Level] )
RETURN
    IF ( currLevel <= 1, SUM ( Table[vmbo b] ) )
        + IF ( currLevel <= 2, SUM ( Table[vmbo k] ) )
        + IF ( currLevel <= 3, SUM ( Table[vmbo (g)t] ) )
        + IF (
            currLevel <= 4,
            SUM ( Table[havo] ) + SUM ( Table[vwo] )
                + SUM ( Table[vwo] )
        )

Regards,

Xiaoxin Sheng