How to calculate the average by dividing instead of sum up first?

[Silvia_Gulu_BXL]

Hello I have a table like this:

And I calculated how much a project is delayed based on how many tasks are delayed.

So, the on-time rate of the projects is:

And now I want to calculate to get the average on-time rate of "Pro 1" and "Pro 2", which is (0+0.67)/2=0.335.

I wrote a dax which is:

And I got the result of 0.4 - this isn't what I want.

I want to divide to get the on-time rate of each project and then average it.

Could you help with that, please?

Thank you in advance!

Kind regards,

Silvia

[Anonymous · Accepted solution]

Hi, @Silvia_Gulu_BXL 

I'm happy to answer your questions. The reason for calculating 0.4 is as follows: since the total row does not have the filtering function, unlike the previous two rows which have external filters Pro1 and Pro2. In the DAX you wrote, 2/5=0.5, in your table there is Two lines of task delay = No, the total number of lines is 5 lines. In summary, the total line shows 0.4. We can use the following dax to find the average on-time rate of "Pro 1" and "Pro 2":

we can generate a summary table through summarize DAX formula :

Summary table formula:

Table = SUMMARIZE('Sheet1','Sheet1'[Project ID],"Project on-time rate",ROUND(DIVIDE(CALCULATE(DISTINCTCOUNT(Sheet1[Task ID]),'Sheet1'[Task delayed?]="No",ALLEXCEPT('Sheet1','Sheet1'[Project ID])),CALCULATE(DISTINCTCOUNT('Sheet1'[Task ID]),ALLEXCEPT(Sheet1,'Sheet1'[Project ID])))+0,2))

The summary results are as follows:

Find the average formula:

Project on-time rate1 = 
SUMX('Table','Table'[Project on-time rate])/DISTINCTCOUNT('Table'[Project ID])

The effect is as follows:

I have provided the PBIX file below, if this helps you, that would be great.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[AmiraBedh]

The average of Project ID =2/3 = 0.6666666666666667

When Power BI calculates the measure for the on-time rate and the average on-time rate, it uses the full precision of the number in the calculation. So internally, the calculation for a project with 2 out of 3 tasks on time would indeed be 0.6666666666666667.

fter the calculation, when Power BI displays the number in a visual, it applies the formatting settings. By default, it might round the number to two decimal places, hence displaying 0.67 instead of the full precision.