How to calculate the percentage of the average number of days late per controller

[Anonymous]

Hello,

I am trying to calculate the average number of days late per controller. E.g. Francis' average delay.

I tried to create a calculate column using DIVIDE(Abs(Days Late); No. of audits performed;0) but no. of audits performed is not coming up as a possible denominator. Could this be because "No. of audits" is from a different query?

Could you please help me 🙂 You may see the below snippets for reference

Thanks in advance,

Anne

[Anonymous]

Add a calculated column on the "no of audits" dataset. 

Average number of days late =
VAR __divide =
    DIVIDE ( RELATED ( Dates[Days Late] ); 'No of audits'[No. of audits preformed] )
RETURN
    IF (
        'No of audits'[No. of audits preformed] = 0;
        RELATED ( Dates[Days Late] );
        __divide
    )

[Anonymous]

When I enter:

Average number of days late =
Var_divide=
Divide(Related(

No possible columns come up in the related function.

Do you know why this is ?

Thanks 

Anne

[Anonymous]

Do you have a relationship set up between the two datasets? 

@Anonymous 

Take a look at the sample PBIX file that I've created. 

https://1drv.ms/u/s!AnIEh6WhI4Jogr5cTQhBacsSJue5kg?e=lkevpn

[Anonymous]

@Anonymousthanks so much for your help so far!

There is a one to many relationship between dates and no. of audits - I think this may be why the formula isnt working. 

If I create a calculated column in the dates table I can choose columns in the no of audits table but not the other way around , only in this direction.

Please see below snippet for reference.

[Anonymous · Accepted solution]

You could try adding the following calculated column to your "Dates" dataset. 

Average number of days late =
VAR __divide =
    DIVIDE ( Dates[Days Late]; RELATED ( 'No of audits'[No. of audits preformed] ) )
RETURN
    IF (
        RELATED ( 'No of audits'[No. of audits preformed] ) = 0;
        Dates[Days Late];
        __divide
    )

If this doesn't produce the correct results, could you give me an example of what output you would expect in the following scenario:
Dates

Controller	Days Late
John	-500
John	-100

No of audits

Controller	No of audits
John	10