pbi calculated table, i can't get the correct average score of the agent if they only have 1 data in

[NoteDaxSolution]

in pbi calculated table, i can't get the correct average score of the agent if they only have 1 data in the dataset but the other agents with multiple data shows the correct result. I am using this dax formula: Score table = SUMMARIZE( PLDT, PLDT[Agent Name], "Overall Score", AVERAGEX(PLDT, PLDT[OVERALL SCORE]) ). Thank you so much in advance.

[v-pgoloju]

Hi @NoteDaxSolution,

Could you please provide a sample PBIX file or sample data along with the expected output as suggested by @MFelix  so that we can assist you more effectively?

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @NoteDaxSolution,

Could you please provide a sample PBIX file or sample data along with the expected output as suggested by @MFelix  so that we can assist you more effectively?

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @NoteDaxSolution,

Just following up to see if the responses provided by community members were helpful in addressing the issue. If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[NoteDaxSolution]

Good Day and Thank you for your help. Unfortunately, the given formula is not working. 

[MFelix]

Hi @NoteDaxSolution ,

Are you able to share a sample file? It seems to me that the test that are being done don't match your model.

If you can share a mockup it will be easier to check the changes that need to be done to the DAX.

[Azadsingh]

hi @NoteDaxSolution  , Please try below dax if this helps: 

Score Table =SUMMARIZE(VALUES(PLDT[Agent Name]),PLDT[Agent Name],"Overall Score",CALCULATE(
AVERAGE(PLDT[OVERALL SCORE])))

Regards,

Azad

[NoteDaxSolution]

Good Day and Thank you for your help. Unfortunately, the given formula is not working. 

[v-pgoloju]

Hi @NoteDaxSolution,

Try using below dax.

Score table =
ADDCOLUMNS(SUMMARIZE(PLDT, PLDT[Agent Name]),"Overall Score",CALCULATE(AVERAGE(PLDT[OVERALL SCORE])))

Thanks & Regards,

Prasanna Kumar

[NoteDaxSolution]

Good Day and Thank you for your help. Unfortunately, the given formula is not working. 

[v-pgoloju]

Hi @NoteDaxSolution,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @MFelix and @Jihwan_Kim  for prompt and helpful responses.

Just following up to see if the solutions provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[NoteDaxSolution]

Apologies for late response and thank you for your suggestion. just got back from sickness. upon testing it resulted with the same issue. The issue is when the agent have only 1 score in the dataset, it shows incorrect result but if the agent have multiple score in the dataset it provides the correct score. it seems that if the agent have only 1 score it average the score in overall score of the dataset resulting an incorrect result.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

I assume you are having problem with how to handle blank rows in the table when calculating average, and I assume you want to include the blank rows into calculating the average.
Please check the below picture and the attached pbix file if it suits your requirement.

Score table = 
SUMMARIZECOLUMNS (
    PLDT[Agent Name],
    "Overall Score", DIVIDE ( SUM ( PLDT[OVERALL SCORE] ), COUNTROWS ( PLDT ) )
)

[NoteDaxSolution]

Apologies for late response and thank you for your suggestion. just got back from sickness. upon testing it resulted with the same issue. The issue is when the agent have only 1 score in the dataset, it shows incorrect result but if the agent have multiple score in the dataset it provides the correct score. it seems that if the agent have only 1 score it average the score in overall score of the dataset resulting an incorrect result.

[MFelix]

Hi @NoteDaxSolution ,

Withou further information is difficult to give you a correct answer, can you please share some mockpup data and expected result.