Forum Discussion

phelms's avatar
phelms
Frequent Visitor
8 years ago
Solved

Count results with dynamic filtering

I am trying to count how many agent that is on target at least 75% of the time in percentage of total number of agents on work in the same period, and show that on a single card.    In a table, tha...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI phelms,

     

    You can use below formula to get the total percent with conditions:

    100% / 75% Taget Percent =
VAR temp =
    SUMMARIZE (
        'Sample',
        [fullname],
        "Pecent", CALCULATE (
            SUM ( 'Sample'[on target] ) / SUM ( 'Sample'[at work] ),
            ALLSELECTED ( 'Sample' ),
            VALUES ( 'Sample'[fullname] ),
            VALUES ( 'Sample'[week] )
        )
    )
RETURN
    FORMAT (
        COUNTROWS ( FILTER ( temp, [Pecent] = 1 ) )
            / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
        "Percent"
    )
        & " / "
        & FORMAT (
            COUNTROWS ( FILTER ( temp, [Pecent] > 0.75 ) )
                / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
            "Percent"
        )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI phelms,

 

You can use below formula to get the total percent with conditions:

100% / 75% Taget Percent =
VAR temp =
    SUMMARIZE (
        'Sample',
        [fullname],
        "Pecent", CALCULATE (
            SUM ( 'Sample'[on target] ) / SUM ( 'Sample'[at work] ),
            ALLSELECTED ( 'Sample' ),
            VALUES ( 'Sample'[fullname] ),
            VALUES ( 'Sample'[week] )
        )
    )
RETURN
    FORMAT (
        COUNTROWS ( FILTER ( temp, [Pecent] = 1 ) )
            / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
        "Percent"
    )
        & " / "
        & FORMAT (
            COUNTROWS ( FILTER ( temp, [Pecent] > 0.75 ) )
                / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
            "Percent"
        )

 

Regards,

Xiaoxin Sheng

phelms's avatar
phelms
Frequent Visitor
8 years ago

Worked like a charm.

 

Thank you very much.