Help! Calculate average in pivot table

phuang - you must have something else going on. Your data totals 200. See this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sylbSUTIAYiMDKMMYwojViVYKycwFsk2h2BDGMILIBpemQrWgYpAc0OBKkBYDmD5DmDyQB1LgmJOZnApXgSQJURELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

When I load that into Power BI and create a quick matrix, I get this:

Sue is correct at zero.

Please post more details on what is wrong if this doesn't help. See steps below to provide data in the forums in a usable format. Thanks!

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Hi phuang ,

In Power BI, the total is not 250. The calculation logic is as below:

Please create a measure like so:

Avg =
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Attribute] ) ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALL () )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.