Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Calculation difference between Power BI and Excel

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.

2 Replies