Forum Discussion
averagea calculation
Hi
Issue 1: I have 5 rows with data as "1" but in the visual only one "1" is displayed.
Issue 2: Avga result is 0.00
@aserpiva64
Issue 1:In the visual only one "1" is displayed.
The table automatically filters duplicate values, so you need to put another column of non-duplicate values in the table, and “1” will be displayed
Issue 2: Enter boolean values such it calculates as "0" and "1"
You can enter the power query editor and input the following codes to the “Advance Editor”
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yhrNM4CxTMMs/LxXIDinPB/NCgkJdgVw3R59gV6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"col 1" = _t, #"col 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"col 1", type text}, {"col 2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Value.FromText([col 1]),type number) then 1 else 0), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Boolean"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"col 1", "Boolean", "col 2"}) in #"Reordered Columns"It will display a new table like the following picture, then apply the data to the desktop.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Xinru Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi RamaKrishna8989 ,
What is your logic in getting the average a column that is of type text? Column 2 has both numbers and letters so it is a text.
Hi danextian.
AverageA handles Text and Non-numerics. Hence i had to select Text datatype. If that is incorrect which datatype should i select?
See here: https://dax.guide/averagea/
It is useless to use this function in DAX with a string column because the result is always 0, resulting in a different result compared to the corresponding AVERAGEA function in Excel. In order to calculate the average of the numbers included in a column with a string data type, use AVERAGEX instead of AVERAGEA converting the column into a number using VALUE
Try that suggestion instead:
AVERAGEX ( AAASheet1, VALUE ( AAASheet1[Col 1] ) )
Issue 1:In the visual only one "1" is displayed.
The table automatically filters duplicate values, so you need to put another column of non-duplicate values in the table, and “1” will be displayed
Issue 2: Enter boolean values such it calculates as "0" and "1"
You can enter the power query editor and input the following codes to the “Advance Editor”
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yhrNM4CxTMMs/LxXIDinPB/NCgkJdgVw3R59gV6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"col 1" = _t, #"col 2" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"col 1", type text}, {"col 2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Value.FromText([col 1]),type number) then 1 else 0), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Boolean"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"col 1", "Boolean", "col 2"}) in #"Reordered Columns"It will display a new table like the following picture, then apply the data to the desktop.
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Xinru Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
