Date slicer is adding instead of averaging unique values

[cmd105]

Hi everyone,

I am having an issue for the last 2 weeks that is completely wrecking my head and would appreciate any inputs you can provide me.

Summary:
I have a dataset with analyst names and their response time along with a few other fields. Depending on how good/bad the response time id, the analyst is put into a “bucket” field that goes from 1 to 5.
So far so good as this is happening.

Problem:
I have a dimension called “month” in each line and I have a slicer to filter out the months. The problem is that the slicer is not averaging the response time.

Dataset example:
(Month | Name | Country | Bucket | Avg Time of response)
March - Jake - UK - 4 - 4.27
March - Lori - USA - 2 - 2.10
April - Jake - UK - 3 - 3.39
April - Lori - USA - 3 - 4.8

At the moment if I select March and April on the slicer I am getting the exact same representation in the graph from the data above. This is basically duplicated data.

What I need is for the average response time of Jake to be averaged out for both months and a new bucket calculated so it is only showing 1x time p analyst.

So for Jake he has 4.27(march)+3.39(April)/2= 3.83 new average.
(Then my bucket variable will calculate the new bucket which would be 3 here)

Hence if I am filtering by March and April I should only see Jake’s new average and not both values!

Here is my pbix: https://www.dropbox.com/s/p64ki7ogjya87h5/testpbi.pbix?dl=0

Would really appreciate some help here. Thanks!

[v-jiascu-msft]

Hi @cmd105,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @cmd105,

To be honest, I don't know the meaning of these results. But the solution could be creating a new table [Buckets]. Because the calculated column can't be evaluated dynamically. We can take it as a normal column. Please check out the demo in the attachment.

Measure =
VAR avghour =
    AVERAGE ( Sheet1[AvgHour] )
VAR bucket =
    SWITCH (
        TRUE (),
        avghour < 2.5, 1,
        avghour >= 2.51
            && avghour <= 4.49, 2,
        avghour >= 4.5
            && avghour <= 5.39, 3,
        avghour >= 5.4
            && avghour <= 6.29, 4,
        avghour >= 6.3, 5,
        BLANK ()
    )
RETURN
    IF ( bucket = MIN ( Buckets[Value] ), [REPActualclosed], BLANK () )

Best Regards,
Dale