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 try to use below measure to calculate on target percent.

 

Formula:

On target Percent =
CALCULATE (
    SUM ( 'Sample'[on target] ) / SUM ( 'Sample'[at work] ),
    ALLSELECTED ( 'Sample' ),
    VALUES ( 'Sample'[fullname] ),
    VALUES ( 'Sample'[week] )
)

 

Result:

 

Regards,

Xiaoxin Sheng

phelms's avatar
phelms
Frequent Visitor
8 years ago

Almost there. 

 

What I want to do, is to count how many agents were on target 100% as a percent of whole (in this example 2/7 = 28.6%), and how many agents were on target at least 75% of the time (3/7 = 42.9%).

 

Can I do that?

  • 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.