topic Re: pbi calculated table, i can't get the correct average score of the agent if they only have 1 data in in DAX Commands and Tips

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

NoteDaxSolution — Thu, 31 Jul 2025 08:07:56 GMT

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.

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

MFelix — Thu, 31 Jul 2025 08:36:24 GMT

Hi NoteDaxSolution ,

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

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

Jihwan_Kim — Fri, 01 Aug 2025 04:20:50 GMT

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 ) ) )

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

Anonymous — Mon, 04 Aug 2025 04:27:34 GMT

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

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

NoteDaxSolution — Mon, 04 Aug 2025 05:15:37 GMT

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.

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

NoteDaxSolution — Mon, 04 Aug 2025 05:16:09 GMT

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

Anonymous — Tue, 05 Aug 2025 06:52:09 GMT

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

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

Azadsingh — Tue, 05 Aug 2025 08:47:21 GMT

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

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

Anonymous — Fri, 08 Aug 2025 02:45:36 GMT

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

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

NoteDaxSolution — Fri, 08 Aug 2025 02:48:54 GMT

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

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

NoteDaxSolution — Fri, 08 Aug 2025 02:49:09 GMT

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

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

NoteDaxSolution — Fri, 08 Aug 2025 02:49:17 GMT

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

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

MFelix — Fri, 08 Aug 2025 12:41:35 GMT

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.

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

Anonymous — Wed, 13 Aug 2025 05:47:18 GMT

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

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

Anonymous — Wed, 20 Aug 2025 06:26:36 GMT

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