Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Normally there is a tiny summation icon next to those column names having whole number as the data type. However, if I take a look at the Fields pane image as attached, it seems not the case. There is no such icon next to “Amount” of Actuals table and “Amt” of Budget table respectively while the corresponding “Amount” and “Amt” columns of FunctionfnBlend table do have that icons. Is some one would like to explain for me what’s the difference and how to adapt the same logic to the mentioned columns of Actuals table & Budget table?
Solved! Go to Solution.
Hi @julianchen,
For the two problem columns, change the Default Summarization to “Sum” as shown in the following screenshots.
Thanks,
Lydia Zhang
Hi Lydia,
You are really awesome, You found the root cause and solved it. I've tried to change it's value setting from report view to return the total value , but the column head would add the wording "Sum of " automatically and the tiny icon still invisible. Now that I realized the real cause. Thank you very much.
Regards,
Julian
Hi @julianchen,
Do you use Chinese version of Power BI Desktop? Would you mind uploading your PBIX file to OneDrive and posting shared link of it here?
Thanks,
Lydia Zhang
Hi Lydia,
My Power BI Desktop is 64-bit English version. I've put my file onto OneDrive with the following link:
https://1drv.ms/u/s!AvN2Aktu3CqBgrQHP7QjYAE7ylR-tw Please check it out. Thank you very much.
Cheers,
Julian
Hi @julianchen,
For the two problem columns, change the Default Summarization to “Sum” as shown in the following screenshots.
Thanks,
Lydia Zhang
Hi Lydia,
You are really awesome, You found the root cause and solved it. I've tried to change it's value setting from report view to return the total value , but the column head would add the wording "Sum of " automatically and the tiny icon still invisible. Now that I realized the real cause. Thank you very much.
Regards,
Julian
Hi Julian,
I cannot replicate this result. When I'm merging and expanding the columns, they turn out with the sum-symbol.
Would you mind sharing your code for further investigation?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Please take a look at the Fields pane: There is no icon next to the Amount column under Actuals table and neither to the Amt column under Budget table even though both columns are of whole number data type. What confused me was that both Quantity and Qty do have a summation icon each placed to the left hand side? Above mentioned two tables are the source tables. I'm not talking about the function query (table) you created, it's no problem.
This looks like a bug in your version.
In my version all number fields a showing correctly (same code):
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Please take a look at the Fields pane: There is no icon next to the Amount column under Actuals table and neither to the Amt column under Budget table even though both columns are of whole number data type. What confused me is that both Quantity and Qty do have a summation icon placed to the left hand side?
Hi @julianchen,
What is your data source and what is the data type of mentioned columns(“Amount” of Actuals table and “Amt” of Budget table) in the original data source? Have you checked that if the data type of mentioned columns is whole number in Power BI Desktop? Could you please provide sample data of Actuals table and Budget table with which I can test?
Also ensure that you use the latest verison of Power BI Desktop(2.43.4647.541).
Thanks,
Lydia Zhang
The following two queries are the source data. You can see the data type of the mentioned columns is whole number using my powerBI desktop with the latest Version: 2.43.4647.541 64-bit (2017年2月). It's a pity I can't' attach the pbix file here, otherwise, it would be more convenient for our discussion.
Actual Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lQwMDAzAFIpRiddCkjSDSRmACLm2EptsYTGBKQ3WbgAml2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Account = _t, Amount = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}, {"Quantity", Int64.Type}})
in
#"Changed Type"
Budget Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc3LCUAxCETRXlyHoOPnFRPsv40EA1n4QK6bA7MWscxzYPlokDDzeQCqlKMBXBAR1T/QC9y9+gDahKpWH9AGzKxKmRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, AccountNo = _t, Amt = _t, Qty = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"AccountNo", type text}, {"Amt", Int64.Type}, {"Qty", Int64.Type}})
in
#"Changed Type"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |