Descriptive Statistics on multiple columns

[AmaGreen]

Hello Community,

I have a dataset that I need to perform calculate the average, minimum and maxim based on the specific categories of items. 

I would like an assistance on how to perform the descriptive statistics. As an example, I would like to calculate the average of Item "A" for the three day period, likewise the minimum and maxim of the item "A". I have attempted grouping them or using Switch true but it does not work.

I have included a sample file in this message.

Thank you in advance for your assistance.

Individual	Item name (Day 1)	Item quantity	Item name (Day 2)	Item quantity	Item name (Day 3)	Item quantity
1	A	2	C	4
2	B	4	D	1	A	5
3	A	1	B	2
4	C	4	B	1	A	2
5	C	2
6	B	6	A	5	C	3
7	C	2	B	1	A	2
8	D	7	A	3	B	3
9	B	1	C	2
10	A	3			B	1

[AmaGreen]

Hello @v-shex-msft 

Thank you for the response and suggestion. 

Please can you write an illustrative example to help me understand your solution better? Alternatively, if there is a resource you can direct me to, I would greatly appreciate it .

Looking forward to your further assistance.

Thanks.

[v-shex-msft]

HI @AmaGreen,

I find a simple way to do these, you can merge these columns of groups and unpivot and spilt these columns to convert these records to suitable table format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY47EoAgDESv4lBTSPhpKXoLhvtfwxgWDWOxRXb3JanVOGPNwSLWyQqsRdRsFbfAvVijHSX1mBxaNLFBbSyKJUkj0o/RbAKT3nu97SXNiv1v3vBrhuvR6uyumPn+k7pVQeOlXm/tBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Individual = _t, #"Item name (Day 1)" = _t, #"Item quantity" = _t, #"Item name (Day 2)" = _t, #"Item quantity.1" = _t, #"Item name (Day 3)" = _t, #"Item quantity.2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual", Int64.Type}, {"Item name (Day 1)", type text}, {"Item quantity", Int64.Type}, {"Item name (Day 2)", type text}, {"Item quantity.1", Int64.Type}, {"Item name (Day 3)", type text}, {"Item quantity.2", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Item quantity", type text}}, "en-US"),{"Item name (Day 1)", "Item quantity"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Item quantity.1", type text}}, "en-US"),{"Item name (Day 2)", "Item quantity.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"2"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Item quantity.2", type text}}, "en-US"),{"Item name (Day 3)", "Item quantity.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"3"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"Individual"}, "Day", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute", type text}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Then you can write Dax expression to simply compare between these fields instead of calculate across multiple table fields.

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @AmaGreen,

You can create a new table with three type of aggregate modes, then you can use it as source of slicer to choose the corresponding calculate mode.

After these steps, you can write measure formula with switch function to check the current selected value and redirect to different calculate expressions.

Regards,

Xiaoxin Sheng