Forum Discussion
Getting issue in grand total of average
Hello Everyone ,
I am find average of numbers of lines that are but grandtotal of that are showing wrong , actual =567 but in grandtotal its showing 68. How can we fixed this issue .
lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi SunilRaj ,
Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.
You can create measure and use IF()+HASONEVALUE() function to determine whether it is Total or not, and if it is, you can do the calculation according to your needs, such as Average, Sum, etc..
If the Total line realizes sum, you can use the following measure:
Sum_meausure = var _table= SUMMARIZE('Table','Table'[Group],"Value",[Measure]) return IF( HASONEVALUE('Table'[Group]),[Measure],SUMX(_table,[Measure]))If the Total line realizes average, you can use the following measure:
Avg_meausure = var _table= SUMMARIZE('Table','Table'[Group],"Value",[Measure]) return IF( HASONEVALUE('Table'[Group]),[Measure],AVERAGEX(_table,[Measure]))If the above doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi SunilRaj ,
Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.
You can create measure and use IF()+HASONEVALUE() function to determine whether it is Total or not, and if it is, you can do the calculation according to your needs, such as Average, Sum, etc..
If the Total line realizes sum, you can use the following measure:
Sum_meausure = var _table= SUMMARIZE('Table','Table'[Group],"Value",[Measure]) return IF( HASONEVALUE('Table'[Group]),[Measure],SUMX(_table,[Measure]))If the Total line realizes average, you can use the following measure:
Avg_meausure = var _table= SUMMARIZE('Table','Table'[Group],"Value",[Measure]) return IF( HASONEVALUE('Table'[Group]),[Measure],AVERAGEX(_table,[Measure]))If the above doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
