cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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.
Thanks a lot.

2 REPLIES 2

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

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors