Help with a measure

[redhughes]

Hi,

I was wondering if someone could help me solve a problem.

The data I have looks like this:

What I want to achieve is a table that shows how many visits each client had, how many distinct employees worked for the client, and an 'distinct employee-to-visits' ratio. Then, ideally there would be a row or another visual that states how many clients' ratio was below our target (let's say 0.428).

So far I've got:

NoOfVisits = count(Sheet1[Date])

DistinctEmployees = DISTINCTCOUNT(Sheet1[Employee])

EmployeesToVisitsRatio = ROUNDDOWN([DistinctEmployees]/[NoOfVisits],2)

And the table looks like this:

Ideally the solution would enable filtering on week, so it would tell me that in week 1 we had x number of clients hitting the target and in week 2, y number of clients.

Any suggestions? Thank you in advance!

[v-ljerr-msft · Accepted solution]

Hi @redhughes,

---

 Then, ideally there would be a row or another visual that states how many clients' ratio was below our target (let's say 0.428).

If I understand it correctly, you should be able to use the formula below to create a new measure to calculate how many clients' ratio was below the target(0.428). 

Measure =
SUMX (
    SUMMARIZE (
        Sheet1,
        Sheet1[Client],
        "abc", IF ( [EmployeesToVisitsRatio] < 0.428, 1, 0 )
    ),
    [abc]
)

Regards

[redhughes]

worked like a dream, thank you so much!!

[redhughes]

I will have a go with this after xmas - many thanks!

[Greg_Deckler]

Add a Slicer visualization based upon Week column?

[redhughes]

I'm mainly after another measure/way of showing count of clients with the ratio below a certain level. Is the slicer going to achieve this?