Calculation difference between Power BI and Excel

[Syndicate_Admin]

Hello!! How are they? I hope well.
I wanted to know if you can help me with a case where I do not understand where the problem would be, I pass the context:
I have a table called Tons, which has the columns:

Id: is the customer number
Date: date of registration
Financial year: number of the accounting year
Ton: quantity of tons

I need to get the tons per exercise of each client (id), the participation of the client in each exercise and finally the average of the last 5 exercises of a client.

For all this create the following measures:

To calculate the sum of tonnes for a specific customer in a specific accounting year:

Tn Client = CALCULATE (
SUM(tonnes[tonnes]),
ALL(tonnes),
Tons[Id] = SELECTEDVALUE(Tons[Id]),
VALUES(tonnes[exercise])
)

To calculate the sum of tonnes for a specific accounting year in all rows of the "Tonnes" table:

Tn Exercise = CALCULATE (
SUM(tonnes[tonnes]),
ALL(tonnes),
VALUES(tonnes[exercise])
)

To calculate a customer's engagement:

participation = [Tn Client]/ [Tn Exercise]

Finally to get the average participation of the last 5 exercises, I made the following measurement:

promedioParticipacion5 =

WHERE _max = .MAX(tonnes[exercise])

WHERE _min =_max-4

RETURN

CALCULATE (

AVERAGEX (

FILTER (

Tons

Tonnes[Exercise] >= _min && Tonnes[Exercise]<=_max

),

[participation]

)

)
I assemble the board leaving it like this:
In the example I chose the id 2553 and the average participation of the last 5 exercises is 22.78.
Now if I calculate the average in an excel sheet the average it gives me is 25.61

I do not understand where the error is in my measurements so that it does not give me the correct value.
Please could you help me???
Thanks a lot.

[Syndicate_Admin]

Hi Admin! Could you pass me the untranslated formula? Because I don't understand it. Thank you.

[amitchandak]

@Syndicate_Admin , Have separate table with values exercise

an try like

New measure =
var _max = MAX(exercise[exercise])
var _min =_max-4
RETURN
calculate(
AverageX(summarize(tonnes,Tons[Id]) CALCULATE (SUM(tonnes[tonnes]) )), filter( all(exercise), exercise[exercise] >=_min && exercise[exercise] <=_min))

Consider window function if needed

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f