The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
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.
Hi Icey,
Yes, the measurement works. Thank you so much for explaining it to me and share the report. Really appreciated!
Penny
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |