Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Team.
i have the below example, with 5 columns (Name,Unit,Training,Group,Status)
Name Unit Training Group Status
A001 CS course1 Group1 Attended
A001 CS course2 Group1 Attended
A001 CS course3 Group1 Attended
A001 CS course4 Group1 Attended
A001 CS course5 Group1 Attended
A001 CS course6 Group1 Attended
B002 IT course1 Group1
B002 IT course2 Group1 Attended
B002 IT course3 Group1 Attended
B002 IT course4 Group1 Attended
B002 IT course5 Group1 Attended
B002 IT course6 Group1 Attended
C003 LE course1 Group1
C003 LE course2 Group1
C003 LE course3 Group1
C003 LE course4 Group1 Attended
we need to know total number of staff completed all training they should complete based on the above example the result should be
A001 100%
B002 83%
C003 25%
so the filnal result should be 1 staff completed 100%
how to create a measure to give me this output?
Solved! Go to Solution.
Hi @mhrkhd ,
Clear. Please try this:
100% Count =
COUNTX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Name],
"completed%",
DIVIDE (
CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) ),
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Name] = MAX ( 'Table'[Name] )
&& 'Table'[Status] <> BLANK ()
)
)
),
[Completed%] = 1
),
[completed%]
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
Hi,
i think there is a much simplier way but it functions
If this post is useful to help you to solve your issue consider giving the post a thumbs up
and accepting it as a solution !
thanks but what you mean by (t) in this
i tried to put t but it gives error
Hi @mhrkhd ,
Please try:
Completed % =
VAR _total = CALCULATE(COUNTROWS('Table'),'Table'[Name]=MAX('Table'[Name]))
VAR _complete = CALCULATE(COUNTROWS('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Status]<>BLANK())
RETURN
DIVIDE(_complete,_total)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
thanks Gao but i need from the Table a Measure to tell me that there is one person completed 100%, how to create the measure from the one you created? do we have to change it or to create another measure based on this one.
Hi @mhrkhd ,
Clear. Please try this:
100% Count =
COUNTX (
FILTER (
SUMMARIZE (
'Table',
'Table'[Name],
"completed%",
DIVIDE (
CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) ),
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Name] = MAX ( 'Table'[Name] )
&& 'Table'[Status] <> BLANK ()
)
)
),
[Completed%] = 1
),
[completed%]
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |