Latest Dates for Scores


#1

Hi All

Please find a dummy setup below with components that are included in my model.

A location can have many Sites and I am trying to find the Average Score for the Location based on the Latest Score in the Context.

The Question I have is how would the measure look to find the Average Score by Location using the Latest Scores in the Current Filter Context if there is no Site on the Table. There is a chance that the Date for Site A Score and Site B Score are different and if I simply use MAXX then only one of the Dates would be in the Context.

I also want to do this with Definitive Latest Dates no matter what Dates are selected in the Context.

Hope this makes sense

Thanks

Chris

Model

Site Data

image

Scores

image

Table with the 2 Measures

image

Measures

Average Score = AVERAGE(Scores[Score])
Average Location Score = CALCULATE(AVERAGE(Scores[Score]), ALL(Factories[Site]))


#2

Can you send me the model Chris. I want to test a few things and can’t imagine it all. Chrs


#3

Hi Sam

Sent to info email address.

Thanks

Chris


#4

Hi Sam, Can you please share the solution. I am also facing the same problem. I am trying to show lates productivity for employees in a table.
Thanks, Chris for the post.


#5

Hi Chris,

Hopefully I’ve got the logic correct in my mind here.

I’m thinking you want to find the average score per location. Say for example one location has two sites, you want the average of the last scores across both these sites.

Does this get you what you need?

AVERAGEX( 
    SUMMARIZE( Scores, Factories[Site],
        "Last Date", LASTDATE( Scores[Date] ),
        "Average Score", CALCULATE( [Average Score], FILTER( ALL( Scores[Date] ), Scores[Date] = LASTDATE( Scores[Date] ) ) ) ),
            [Average Score] )

#6

Hi Sam

Thanks for the reply.

It isn’t correct as it should pickup the SiteID 1 - 01/04/2018 result of 4 and SiteID Result would be 05/04/2018 of 2 which Averages @ 2 giving an Location Average of 3.

Hope that makes Sense.

Chris


#7

The only thing to note here is do you care about the the context from the slicer?

image

I’m working on this, based on not caring.


#8

This gets you the correct last date (regardless of month selected)

Now need to get correct score.


#9

This is what I’ve come up with Chris. Maybe it could be more eloquent, but it works

First calculate below

Average Location Score Latest = 
VAR MaxSiteDate = CALCULATE( LASTDATE( Scores[Date] ), ALL( Scores ), VALUES( Scores[SiteID] ) )

RETURN
AVERAGEX(FILTER( ALL( Scores ),
   Scores[Date] = MaxSiteDate ),
       [Average Score] )

The total is wrong I understand. At the present time I can’t work out the perfect one measure solution, so I have jumped to another measure to finish this off.

This sorts out the total

Avg. Latest Location Score = 
IF( HASONEVALUE( Scores[SiteID] ),
    [Average Location Score Latest],
        AVERAGEX( SUMMARIZE( Scores, Factories[Location], Factories[SiteID] ),
            [Average Location Score Latest] ))

I’ll need to in the future investigate how to get this into the one formula, but have to move on for now as spent some time working on this one.

Chrs
Sam


#10

Hi Sam

Thanks for the Measures.

It isn’t quite correct in that while the Grand Total is Correct I would need the Avg. Latest Location to show the 3 on both lines of the Table. Does that make sense.

Thanks

for all your help so far.

Cheers Chris


#11

I think you could just wrap the final formula in CALCULATE and then use ALL( Site Column) in the filter area.

Can you try that.


#12

I just tried this to test and seemed to work out.

Avg. Latest Location Score 2 = 
CALCULATE( [Avg. Latest Location Score], ALL( Factories[Site] ) )