Forum Discussion

MartijmH's avatar
MartijmH
Frequent Visitor
3 years ago

Summing a measure

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):

NaamJaarTargetTypeNummerNaam | Jaar
Jan Janssen20202INS AI001Jan Janssen | 2020
Jan Janssen20202INS BI002Jan Janssen | 2020
Jan Janssen20214INS AI003Jan Janssen | 2021
Jan Janssen20214INS BI004Jan Janssen | 2021
Jan Janssen20214INS AI005Jan Janssen | 2021
Jan Janssen20226INS AI006Jan Janssen | 2022
Jan Janssen20226INS BI007Jan Janssen | 2022
Jan Janssen20226INS AI008Jan Janssen | 2022
Jan Janssen20226INS BI009Jan Janssen | 2022
Bart Smit20192OBSOBS001Bart Smit | 2019
Bart Smit20202OBSOBS002Bart Smit | 2020
Bart Smit20212OBSOBS003Bart 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
Need Help
Show and Tell

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      MartijmH
      Frequent Visitor
      3 years ago

      Hi Anonymous,

      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.