Find the rating for employees Count of rating,Percentage , Tolerence Above , filter with department

[Saitharunk]

Table 1 

Table 2

Requirement in power bi

with out filtering with Department the result in power bi.

after filtering with Depatment with "Mech", the result showing in power bi.

Can you please do this requirement in power bi and share me PBX file .

[Anonymous · Accepted solution]

Hi @Saitharunk ,

Please create measure with below dax formula:

Count of Rating =
VAR _rating =
    SELECTEDVALUE ( Table2[Rating] )
VAR tmp =
    FILTER ( ALLSELECTED ( Table1 ), [Rating] = _rating )
RETURN
    IF (
        HASONEVALUE ( Table2[Rating] ),
        COUNTROWS ( tmp ),
        COUNTROWS ( ALLSELECTED ( Table1 ) )
    )

Percentage =
VAR _a = [Count of Rating]
VAR _b =
    COUNTROWS ( ALLSELECTED ( Table1 ) )
RETURN
    DIVIDE ( _a, _b )

Tolerence =
VAR _a =
    SELECTEDVALUE ( Table1[Department] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( Table1[Rating] ),
        FILTER ( ALL ( Table1 ), [Department] = _a )
    )
VAR _rating =
    SELECTEDVALUE ( Table2[Rating] )
VAR sum_te =
    CALCULATE ( SUM ( Table2[Toerance] ), ALL ( Table2 ) )
VAR _te =
    CALCULATE (
        SUM ( Table2[Toerance] ),
        FILTER ( ALL ( Table2 ), [Rating] = _rating )
    )
VAR _val =
    IF (
        NOT ( HASONEFILTER ( Table1[Department] ) ),
        DIVIDE ( _te, sum_te ),
        IF ( _rating IN tmp, DIVIDE ( _te, sum_te ) )
    )
RETURN
    IF ( HASONEVALUE ( Table2[Rating] ), _val, BLANK () )

Above Tolerence =
VAR _a = [Percentage]
VAR _b = [Tolerence]
VAR _val =
    IF ( _a > _b, "Yes", "No" )
RETURN
    IF ( HASONEVALUE ( Table2[Rating] ), _val, BLANK () )

Measure =
VAR _a =
    SELECTEDVALUE ( Table1[Department] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( Table1[Rating] ),
        FILTER ( ALL ( Table1 ), [Department] = _a )
    )
VAR _rat =
    SELECTEDVALUE ( Table2[Rating] )
VAR _val =
    IF ( _rat IN tmp, 1 )
RETURN
    IF ( NOT ( HASONEFILTER ( Table1[Department] ) ), 1, _val )

Please refer the attaced .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.