Help! Calculate average in pivot table

[phuang]

Hi all,

I am trying to get the correct average from a pivot table.

This is a survey to study where people would like to spend $50.

Each person is given $50 to spend and 5 different category.  One person, Sue didn't want to spend on anything.

When I calculate the average in excel, she's not counted.

However, after I unpivot, the total of sum seems to change from 200 to 250.

Table one is the original table: 

	A	B	C	D	E
Mark	0	20	0	30	0	50
Tim	5	5	15	5	20	50
Sue	0	0	0	0	0	0
Mary	10	15	10	0	15	50
Alice	10	10	0	15	15	50
Total	25	50	25	50	50	200

In excel, the average is correct, for example column A average is 25/200 = 0.125

However, the average is incorrect in tabel 2, 25/250 = 0.1

The sum of all values should be 200, not 250.

I don't know why it is 250 in the pivot table.  Is there a way to fix it?

Table 2: I unpivot table 1 and create table 2

Name       Attribute Value

Mark	A	0
Mark	B	20
Mark	C	0
Mark	D	30
Mark	E	0
Tim	A	5
Tim	B	5
Tim	C	15
Tim	D	5
Tim	E	20
Sue	A	0
Sue	B	0
Sue	C	0
Sue	D	0
Sue	E	0
Mary	A	10
Mary	B	15
Mary	C	10
Mary	D	0
Mary	E	15
Alice	A	10
Alice	B	10
Alice	C	0
Alice	D	15
Alice	E	15

Thank you!

Penny 

[edhans · Accepted solution]

@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.

[Icey · Accepted solution]

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.

[phuang]

Hi Icey,

Yes, the measurement works.  Thank you so much for explaining it to me and share the report.   Really appreciated!

Penny  

[edhans · Accepted solution]

@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.

[phuang]

Hi edhans,

Thanks for your response.  Yes, the data total is 200 in excel and the average is correct in excel but not in power bi. 

In power bi, the average is not calculated with the 200, it avg with 250 so the average number is wrong.

I googled and found that pivot table's average is not calculated by true sum, the total of all numbers.  Is there a way to force it to calculate by the true sum?

[edhans]

I don't know what  you mean. The image I posted is Power BI, and it has no such bug in calculating. It does averages just like Excel.

[phuang]