Forum Discussion
Incorrect Grand total?
Hi,
I know this is somehow a common question. I found some anwsers and tried with my dataset but not successed. Please help. I have table as below with Item code, Item name and its price. The data set has multiple-duplicated records. I wanted to create a measure that will calcuate price/item and show total of price of all items as Grand total. Measure = Sum(Price)/CountRows(Table). I got correct price/item, but not Grand total. Please help. Thanks.
# Item Price
| 1 | A | 2,500,000 |
| 2 | B | 8,711,557 |
| 3 | C | 8,757,010 |
| 4 | D | 4,650,000 |
| 5 | E | 14,200,000 |
| 6 | F | 16,500,000 |
| 1 | A | 2,500,000 |
| 2 | B | 8,711,557 |
| 3 | C | 8,757,010 |
| 4 | D | 4,650,000 |
| 5 | E | 14,200,000 |
| 4 | D | 4,650,000 |
| 5 | E | 14,200,000 |
| 6 | F | 16,500,000 |
| 1 | A | 2,500,000 |
| 2 | B | 8,711,557 |
| 3 | C | 8,757,010 |
(The grand total should be a sum of all price/item: $55,318,567)
= SUMX(VALUES('TABLE'[Item]),CALCULATE(SUM('TABLE'[Price])/COUNTROWS('TABLE')))giangtongle
Here is a link to download a sample solution file:
Incorrect Grand total 2022-08-09.pbix
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
Showcase Report – Contoso By SpartaBI
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook
4 Replies
CNENFRNLCommunity Champion
= SUMX(VALUES('TABLE'[Item]),CALCULATE(SUM('TABLE'[Price])/COUNTROWS('TABLE')))- SpartaBI
giangtongle
Here is a link to download a sample solution file:
Incorrect Grand total 2022-08-09.pbix
In case it answered your question, please accept the solution to help other members find it. Appreciate Your Kudos.
Showcase Report – Contoso By SpartaBI
SpartaBI Logo
Visit SpartaBI website Visit SpartaBI Linkdin Visit SpartaBI Facebook 
SmalflyResponsive Resident
Hi giangtongle ,
your grand total is also calculated according to the formula Sum(Price)/CountRows(Table).
Please have a look at this post on ways for handling with totals for a measure.
BIG Thanks CNENFRNL , Smalfly, and SpartaBI,
Both options with SUMX function works well. I have another question: When I tried with my dataset by using the following syntax, I got a wrong Grand total. Any ideas? Thanks in advance.
ItemCost_Var =VAR TotalCost =SUM('Table'[Price])VAR NoOfRecords =COUNTROWS('Table')RETURNSUMX(VALUES('Table'[#]),TotalCost/NoOfRecords)
