get different results in an excel average and the same average in power bi

[rsibu0412]

I am developing a dashboard of indicators, I have current goals and sales, the way I connect to the data api power bi takes me the goals as follows. 

To resolve this duplication, what I do is take the average and even there perfect, but when totaling the average of goals, it gives me a different number than the average that excel shows (which is correct), 

The average that I show in the image is not correct, performing the calculation the number should be 197.31, I do not understand how to solve that, I have applied AVERAGE, AVERGAREX but it does not work for me, I would greatly appreciate any help.

[rsibu0412]

Thank you very much for your answer, I have the data for each of the sellers I have a goal, those rows are repeated as I show in the image, this is because that seller has other information in other columns but when taking the average of the goal it does it well, but when making a total of the average there is a difference with the excel of + - 0.8%

This image is of power bi when averaging the goals of each of the agents does them well but in the total there is a difference.

this other image belongs to excel with the same data if you can see the results are different, This happens to me doing all the totals, in that data I only filter by teams which is another column that I have but if I remove the filter and put it to calculate all the same it gives a difference, when you talk about grouping what exactly do you mean?

I really appreciate your comments and if you need to see something exactly I can show it.

[v-xulin-mstf]

Hi @rsibu0412

In power bi, some duplicate values are aggregated, which means that some data is filtered in power bi compared to the data in excel.
The average value shown in table visual is the average of the filtered data.

You can use Averagex function to calculate your expected average.

This is what I meant when I said "group the values that need to be averaged" above.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[rsibu0412]

Hello, thanks for the help, I have not been able to apply AVERAGEX because it asks me for an expression, but what I want is to calculate the goalboxes which is a data field, that value comes from the api, because I calculate the average in the graph? I calculate it because for each salerep that value is repeated but when placing the average in the graph it gives me the value I need, but how do I calculate the averagex if that field is not an expression but a value set in the data? I really appreciate the comments.

[v-xulin-mstf]

Hi @rsibu0412

You can use column as an expression, which means that the average value is calculated for that column.

Just like:

= AVERAGEX(filtertable, Table[value])

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[rsibu0412]

I am really grateful for the help, but I still cannot solve the problem, I show the measurement I made with AVERAGEX, that I had tried without success but I do not know if I am wrong.

I keep getting the same result as just placing average on the graph

This is the result you should get. I really appreciate any comment because I still don't understand.

[v-xulin-mstf]

Hi @rsibu0412,

Is there any filters in the visual?

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[rsibu0412]

Yes, I have the dashboard mounted in a project using the javascript api of power bi, then when people enter a link, in that link they send an id that is the supervisor id and the visual automatically filters by that supervisor id and shows The values of the team, both individual and the total, the problem is that when it shows the total of the team, these values are not right, but the individual ones are. If you want you can see the dashboard in operation here.

dashboard-commercial.herokuapp.com/?id=805572 

Very grateful for your responses.

[v-xulin-mstf]

Hi @rsibu0412

Please check if the filter conditions in power bi and excel are the same.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[rsibu0412]

Hello, any help on this case? I can not solve it, any idea would be of great help.

[rsibu0412]

Hi, Yes, it's the same filter. I'm using supervisor id for filter data for team.

[mahoneypat]

You need to "Summarize" the columns used in your visual and use a measure like this.  It references your current measure and will give the same results on the rows, but the average of averages in the total.

NewMeasure = AVERAGEX(SUMMARIZE(Table, Table[SaleRep], Table[TeamName]), [YourMeasure])

Pat

[v-xulin-mstf]

Hi @rsibu0412

Perhaps the filter is affecting the average and you need to group the values that need to be averaged.

Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!