averagea calculation

[RamaKrishna8989]

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

@Syndicate_Admin

@aserpiva64

[Anonymous · Accepted solution]

Hi RamaKrishna8989

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.

[danextian]

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.

[RamaKrishna8989]

Hi @danextian.

AverageA handles Text and Non-numerics. Hence i had to select Text datatype. If that is incorrect which datatype should i select?

[AlexisOlson]

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] ) )

[RamaKrishna8989]

Thanks Alexis. but i am trying to achieve the below output.

I want Text value as Zero and Boolean True as One and return the average. 

My doubts here are:

1) In Col1, why is it considering only one "1" when i have value "1" in five rows

2) How should i enter boolean values such it calculates as "0" and "1"

@danextian @Syndicate_Admin 

[danextian]

Hi @RamaKrishna8989 ,

You can  just create a calculated column to convert the strings in column to your desired number equivalent use that column instead to get the average. 

Col3 =
VAR _string = 'Table'[Col2]
RETURN
    IFERROR ( VALUE ( 'Table'[Col2] ), ( IF ( _string = "TRUE", 1, 0 ) ) )