Help with a measure for object

[Syndicate_Admin]

Hello, good afternoon!

I'm making a progress board in training courses and I'm glued together so I can show the segmented progress.

Each employee must take 8 courses, those 8 courses are divided into three classifications: HSE (4 courses), Cybersecurity (2 courses) and Corporate (2 courses).

The report shows me for each row if the collaborator did x course or not, I have it as "Done" or "Pending" and also a binary column: Done = 1 and Pending = 0.

For the advancement for the HSE case, I need the % of collaborators who have already taken the 4 courses, knowing that if they did the 4 courses it would be 1 completed and if someone is missing at least 1 course, it would be 0.

I managed to make measurements that show me the following table:

Where:

HSE = calculate([Contributor],'Saba Report'[Rating]="HSE",'Consolidated Collaborators'[Email]='Consolidated Collaborators'[Email])

y

HSE2 = if([HSE]>3,1,0)

But when I try to do the HSE Advance, I get 25% using this measurement:

HSE Advance = [HSE2] / count('Consolidated Collaborators'[Email])

and this is because it does the 1/4 operation.

Could someone help me?

I need to show that the HSE advance is 75% on a card and as much as possible on a pie chart.

I hope I have been clear. Thank you very much for your support.

[v-zhengdxu-msft · Accepted solution]

Hi @Syndicate_Admin 

Please try this measure:

HSE Advance =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Consolidated Collaborators' ),
        'Consolidated Collaborators'[Colaborador],
        "_HSE2", [HSE2]
    )
RETURN
    COUNTROWS ( FILTER ( _vtable, [_HSE2] = 1 ) )
        / COUNT ( 'Consolidated Collaborators'[Email] )

The COUNTROWS ( FILTER ( _vtable, [_HSE2] = 1 ) ) returns 3(the quantity of that HSE2 = 1)

The result is as follow:

Best Regards

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

[Ashish_Mathur · Accepted solution]

Hi,

Try this pattern

Measure = divide(countrows(filter(values(Data[Collaborador[),[hse]>3)),count('Consolidated Collaborators'[Email]))

[v-zhengdxu-msft · Accepted solution]

Hi @Syndicate_Admin 

Please try this measure:

HSE Advance =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Consolidated Collaborators' ),
        'Consolidated Collaborators'[Colaborador],
        "_HSE2", [HSE2]
    )
RETURN
    COUNTROWS ( FILTER ( _vtable, [_HSE2] = 1 ) )
        / COUNT ( 'Consolidated Collaborators'[Email] )

The COUNTROWS ( FILTER ( _vtable, [_HSE2] = 1 ) ) returns 3(the quantity of that HSE2 = 1)

The result is as follow:

Best Regards

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