Get the LAST NON BLANK SCORE Per SubCategory, then AVG the result for the Category Level

[myou]

Get Last Non Blank Score Per SubCategory then Average it all up

DataModel:

Data Model

In Measuress Table I have Score, I want to get the last non blank value per Subcategory

I managed to get the last non blank value per subcategory, but when it comes to Category level the result becomes wrong

I am getting 33% while it should be 28%

DAX USed:

Measures_Score_AVG_Score =
CALCULATE (
    AVERAGE ( Measuress[Measure_Score] ),
    FILTER (
        Measuress,
        Measuress[EndDate]
            = CALCULATE (
                LASTNONBLANK ( Measuress[EndDate], TRUE () ),
                FILTER ( Measuress, Measuress[Measure_Actual] <> BLANK () )
            )
    )
)
​

PBIX FILE : https://gofile.io/d/R2ABeq

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin]

28% is wrong.  The score for Toyota was on Jan 1, not on Feb 1, and should therefore not be considered.

[lbendlin]

33% is correct because the lastnonblank date for "Cars"  is Feb 1 20, and it has two values of .5 and .15, and the average of these is .325

[myou]

Hi @lbendlin , I Want a formula to generate the 28%