Why averagex not showing the correct result

[Anonymous]

Hi all,

Averagex formula :

UniqueTarget = AVERAGEX(DISTINCT(SAP_PPT_EV_MaterialDocument_TotalGR[Target]),SAP_PPT_EV_MaterialDocument_TotalGR[Target])

Why when I used above formula for average, I got the incorrect result 96.13%. It should be 96.06% when I try to average it in excel.

PD4	640	WASHI	640 CH Machine	94.000%
PD4	640	WASHI	640 CD Machine	94.000%
PD4	640	WASHI	640 CK Machine	94.000%
PD4	640	WASHI	640 CG Machine	94.000%
PD4	640	WASHI	640 CC Machine	94.000%
PD4	640	EM DC/T	640 CB Machine	96.900%
PD4	640	EM DC/T	640 CC Machine	96.900%
PD4	640	DC/T	640 CB Machine	97.500%
PD4	640	DC/T	640 CC Machine	97.500%
PD4	640	EPM Polyester	640 CC Machine	97.500%
PD4	640	SPV	640 CB Machine	97.500%
PD4	640	TAPE	640 CC Machine	97.500%
PD4	640	TAPE	640 CD Machine	97.500%
PD4	400	EM DC/T	400 WH Machine	96.000%
PD4	400	DC/T	400 WH Machine	96.000%
PD4	400	WASHI	400 WK Machine	94.000%

Regards,

Nuha

[Anonymous]

Hi @Anonymous ,

Thanks for the reply from @vicky_ .

The AVERAGEX function in Power BI calculates the average of the expressions computed for each row in a table. When you use the DISTINCT function in AVERAGEX, DISTINCT removes duplicate values from the data set before performing the average calculation. This may be the reason you are observing differences.

I did the calculations using the example data you provided and looked at your latest reply and calculated the correct results in your reply.

Please try.

Create a measure:

Measure = 
VAR _count = CALCULATE(COUNTROWS('Table'),ALL('Table'))
VAR _sum = CALCULATE(SUM('Table'[Value]),ALL('Table'))
RETURN
DIVIDE(_sum,_count)

After creating a good drag it to the table visual object, you may see the results displayed as 1, do not panic, set its format as a percentage and then select the number of decimal places you want to retain can be.

This was successful and the page visualization is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hi @Anonymous,

I already try your formula but it give me the wrong value

Regards,

Nuha

[vicky_]

I tried having a go with your data, but couldn't match your results exactly. Since there are no headers provided, I assume that the last column in your data is the target. 

I'm assuming you want the equivalent target value in cell E18 shown above. Note how it is different from the average value shown in cell H6, despite both of them being averages. If your target value is NOT a measure / is not summarized or aggregated, you can use the AVERAGE function (without the X) and that should give you the correct values.

Otherwise, your current measure is grabbing each of the unique target values in E2:E17, (i.e the values in H2:H5), and then taking the average value to get the result of 96.1% in the spreadsheet above. To fix it, you can try something like the below:

New Measure = 
var summaryTable = ADDCOLUMNS(SUMMARIZE(TableName, [ColumnB], [ColumnC], [ColumnD]), "target", [Your Target Measure])
RETURN AVERAGEX(summaryTable, [Your Target Measure])

The idea is that you create a virtual table which matches the data that you're seeing, and then you iterate over each of the rows in that virutal table to get your average.

[Anonymous]

Hi @vicky_ ,

Thank you for your solution but it still do not give me the correct answer. As you can see below I filter 2 different month and the result is different because not all month have all machine running. The correct answer should be 95.93%.

Any formulation that it will not effect the number of machine running or etc?

Regards,

Nuha

Regards,

Nuha