Average of measure not correct

[jasperdavid]

Hey guys,

I'm calculating levels (1-2-3-4) for suppliers based on different measures.

I have a yearly overview where i want a card showing the average level based on the date slicer/period selected. When i select the full year the calculation is correct, but when i drill down on 1 month it should show the value (average for 1 month = value of that month) of that month, but i'm getting a totally different value.

LEVEL = if([Total score]=90;1;if(75<[Total score];2;if(65<[Total score];3;4)))

LEVEL average per Month =

AVERAGEX(

    KEEPFILTERS(VALUES('Date table'[Date].[Month]));

    CALCULATE([LEVEL])

)

so for this one supplier for 2019 example: he had in february and may level 4 (= bad) and the rest of the monts he had level 1.

So when i select the whole year, average level = 1,5 (which is correct:18/12 =1,5).

But if i then click on february or may it should show 4, but it's showing 1,25 ?

i don't know how it is calculated or what i'm doing wrong.level for whole yearselecting may

[PaulDBrown · Accepted solution]

@jasperdavid 

Try:

Average Level = 
AVERAGEX(
    SUMMARIZE(Date Table, 'Date table'[Date].[Month],
    "Levelcalc",CALCULATE([LEVEL]),
    [Levelcalc])

See if that works.

[jasperdavid]

works like a charm! thanks!

[jasperdavid]

hey guys,

I'll try to explain but can't share the file due to security reasons.

On the screenshot you can see the different tables and relations i have.

The suppliers are stored in Supplier data, and through calculations they receive points , that add to a total score which is then changed in to a level by a measure.

Main tables are Notifications and Main list (sample reports). Score is calculated as followed:

LEVEL = if([Total score]=90;1;if(75<[Total score];2;if(65<[Total score];3;4)))

Total score = [Notifications score]+[PPM score]+[Measurement Report score]+[PPM sample score]+[Response time score]+[Management scoreEVAL]

Notifications score =

VAR NumNo = COUNT(Notifications[DENV Notification No.])

RETURN

if(

isblank(

NumNo);

15;

if(NumNo<2;

15;

IF(NumNo<4;

10;

if(NumNo<7;

5;

0))))

PPM score = if([PPM]>1125;0;if([PPM]>375;10;IF([PPM]>75;30;40)))

Measurement Report score = if([NG measuring reports] = 0; 5; IF([NG measuring reports] = 1; 3; 0))

PPM sample score = if ( [PPM Parts inspection] < 5000 ; 15; if([PPM Parts inspection] < 100000; 10; if([PPM Parts inspection] < 500000;5;if([PPM Parts inspection]>=500000;0;0))))

Response time score = if(ISBLANK([Avg 8D response time])=TRUE();5;if([Avg 8D response time]>20;0;if([Avg 8D response time]>15;2;if([Avg 8D response time]>10;3;5))))

Management scoreEVAL = if(ISBLANK(AVERAGE(Notifications[Management score]));10;AVERAGE(Notifications[Management score]))

it's not rocket science but something doesnt seem to be right with the measures as i'm calculating the average it gives strange results. 

Also the =SELECTALL didn't work.

[v-xuding-msft]

Hi @jasperdavid ,

Please share some sample data and your expected results that will make us understand clearly about your requirement.

How to Get Your Question Answered Quickly

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dedelman_clng]

Try replacing KEEPFILTERS() with ALLSELECTED

AVERAGEX( ALLSELECTED('Date Table'[Date]), CALCULATE([Level]) )

If that doesn't work, please provide sample data along with all measure calculations (e.g. [Total Score])

Hope this helps

David