Incorrect Grand total?

[giangtongle]

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)

[CNENFRNL · Accepted solution]

= SUMX(VALUES('TABLE'[Item]),CALCULATE(SUM('TABLE'[Price])/COUNTROWS('TABLE')))

[giangtongle]

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

RETURN

SUMX(

    VALUES('Table'[#]),

TotalCost/NoOfRecords)

[Smalfly]

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.

[SpartaBI · Accepted solution]

@giangtongle 
Here is a link to download a sample solution file:
Incorrect Grand total 2022-08-09.pbix

Showcase Report – Contoso By SpartaBI



      

[CNENFRNL · Accepted solution]

= SUMX(VALUES('TABLE'[Item]),CALCULATE(SUM('TABLE'[Price])/COUNTROWS('TABLE')))