Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Syndicate_Admin
Administrator
Administrator

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:
marcoms_0-1695753341402.png

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

marcoms_1-1695753549384.png

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 2
Syndicate_Admin
Administrator
Administrator

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

amitchandak
Super User
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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