Summing a measure

[MartijmH]

Hi everybody,

I have a dataset where each row is an inspection (INS A & INS B) or an observation (OBS) of an employee (fake data):

Naam	Jaar	Target	Type	Nummer	Naam | Jaar
Jan Janssen	2020	2	INS A	I001	Jan Janssen | 2020
Jan Janssen	2020	2	INS B	I002	Jan Janssen | 2020
Jan Janssen	2021	4	INS A	I003	Jan Janssen | 2021
Jan Janssen	2021	4	INS B	I004	Jan Janssen | 2021
Jan Janssen	2021	4	INS A	I005	Jan Janssen | 2021
Jan Janssen	2022	6	INS A	I006	Jan Janssen | 2022
Jan Janssen	2022	6	INS B	I007	Jan Janssen | 2022
Jan Janssen	2022	6	INS A	I008	Jan Janssen | 2022
Jan Janssen	2022	6	INS B	I009	Jan Janssen | 2022
Bart Smit	2019	2	OBS	OBS001	Bart Smit | 2019
Bart Smit	2020	2	OBS	OBS002	Bart Smit | 2020
Bart Smit	2021	2	OBS	OBS003	Bart Smit | 2021

In each row the target of the employee for that year is shown. So, for example, Jan Janssen has a target of 6 inspections in 2022, but has only done 4 inspections (4 rows).

I want to check for everybody if they reached their target for inspections or not. I use the following measure for calculating the number of inspections for each employee:

(#) Inspecties gevolgd =

CALCULATE(

    COUNT(

        Targets[Nummer]

    ),

    Targets[Type] = "INS A" || Targets[Type] = "INS B",

    Targets[Nummer] <> BLANK()

)

I use the following measure to calculate the target of each employee. The part after RETURN is only for the formatting of my table visual, because I only want the totals to be filled:

(#) Verplichte inspecties =

VAR Verplicht =

SUMX(

    VALUES(Targets[Naam | Jaar]),

    CALCULATE(

        MAX(Targets[Target]),

        Targets[Type] = "INS A" || Targets[Type] = "INS B"

    )

)

RETURN

    IF(

        SELECTEDVALUE(Kalender[Kwartaal]) <> BLANK() || SELECTEDVALUE(Kalender[Maand]) <> BLANK(),

        BLANK(),

        IF(

            SELECTEDVALUE(Kalender[Maand]) = BLANK(),

            Verplicht,

            IF(SELECTEDVALUE(Kalender[Maand]) <> BLANK(),

            BLANK(),

            Verplicht

        )

    )

)

Next, I use the following measure to return a 1 if an employee did not reach 80% of their target yet and a 0 if they did reach it:

(#) Inspecties gehaald =

VAR SelectedMonth = SELECTEDVALUE(Kalender[Maandnr])

VAR SelectedYear = SELECTEDVALUE(Kalender[Jaar])

VAR ThisYear = YEAR(TODAY())

VAR ThisMonth = MONTH(NOW())

VAR NumberOfMonths =

IF(

    SelectedMonth = BLANK() && SelectedYear = ThisYear, SelectedMonth,

    12

)

VAR Target = (1 - 0.8)

VAR Verplichting =

ROUNDDOWN(

    [(#) Verplichte inspecties] * DIVIDE([Number of selected months], 12),

    0

) - ([(#) Verplichte inspecties] * Target)

VAR Result =

IF(

    [(#) Inspecties gevolgd] < Verplichting, 1, 0

)

RETURN

Result

I use the third measure to add a conditional format on each name. If the measure returns 1, I want the name to have a red background.

This all works just fine, however I also want to count the number of names that have a red background (where the third measure = 1). But, I cannot sum a measure of course. Can someone help me with this? Thank you in advance.

MartijmH

[v-stephen-msft]

Hi @MartijmH ,

Since your [Inspecties gehaald] return 1 or 0, you can sum a measure. Try the SUMX function.

Measure = SUMX('TABLENAME', [Inspecties gehaald])

OR try to count the measure.

Measure = COUNTX('TABLENAME', [Inspecties gehaald])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MartijmH]

Hi @v-stephen-msft,

Thank you for your response. I tried both measures, but unfortunately both are not working.

For the year 2022, the SUMX() measure gives back 0 (can't figure out why) and the COUNTX() gives back 4 (because Jan Janssen has 4 inspections out of 6). However, I want it to give me 1 as there is one employee that did not reach his target.