Create Calculated Table using Groupby

[Anonymous]

Hi!

I'm trying to create a new table in Power BI desktop, using groupby or something through DAX. What I'm working on involves a much more complicated data but I'll illustrate my problem through this simple example.

I have a data with somehow a similar structure as this:

I want to create a summarized table (Calculated Table) by region where Count is the number of items by region, Percent is the percentage of items by region, Average Sales is the mean of Sales per region and Target Hit is either HIT or MISS (HIT if Average Sales >= 50 and MISS if Average Sales < 50) 

If I were to code this using Python's Pandas, I'll use groupby and agg to create the desired table. Is there a similar way to create this table using DAX? 

Thanks!

[Gazzer]

I don't know why/whether you specifically need/want to use DAX, but you can achieve this easily by using the built-in functions of Power BI.

In the query editor, use Group By on your Regions field, with aggregation for Count and Average. Then add an conditional column to create the Target Hit column. 

Add these fields to a table in the report. Add the Count column a second time and rename it to Percentage. Then set the field to show as Percentage of Grand Total.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc69DgIxCAfwVzGdLzmg9MvtBp/AODUdHBycNBrfXyjjHQsQ+gv99x4wLAHjCmUlwHpCPgPI6naV0loYSw+khHdk+z7v0pKhqCjt0OX3eb0fMmSYjJVlnzFPlpQV78tot7Ki6kQ3UpS0Y4JkpspMcGxqnKQpQT91sUuoSyIvtjyP8Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Timestamp = _t, Region = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type datetime}, {"Region", type text}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Region"}, {{"Count", each Table.RowCount(_), type number}, {"Average Sales", each List.Average([Sales]), type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Target Hit", each if [Average Sales] >= 50 then "HIT" else "MISS")
in
    #"Added Conditional Column"

The source will be different for you (I just needed a sample of data to demonstrate) and you will want to tidy the values in terms of decimal places and so on.

[Anonymous]

Hi @Gazzer!

Thanks for the reply! I honestly didn't think about using the Query Editor for this. Groupby works well for this aggregation but I forgot to add something. I actually have an additional requirement for the groupby table that my previous example was not able to demonstrate. 

What if I need to create another Percent column (Percent 2), where it is computed as Count/ Count 2. Is this still possible using the Query Editor?

[Gazzer]

Hi @Anonymous

I guess it all depends where the additional Count 2 column fits in to this, but I see no reason why you could not do this in the editor. I would need to know how Count 2 fits in to the rest of the data before I could really look at your options for this. 

[Anonymous]

@Gazzer

Count 2 would be the number of rows given a condition. For example, I have another column in my raw data which is Group. Then, Count 2 would be the number of rows where Group = "A".

(Note: Don't be confused as numbers will not match to the Table above (count of A and B in Group column does not really amounts to what is in the Count 2 column) , I just created this for illustration)

Sorry for not clearing the problem the first time. (Really appreciate your help )

[Gazzer]

Hi @Anonymous

Is this what you mean?

I suspect Percent2 should actually be Count2/Count instead of this way round - you want to know how many of the total count is in Group A, right? 

let
    Source = Excel.Workbook(File.Contents("[yourdrive]\Power BI - Groupby Problem.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Timestamp", type datetime}, {"Region", type text}, {"Group", type text}, {"Sales", Int64.Type}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Timestamp", "Region", "Group", "Sales"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Other Columns", "Count2 (is Group A)", each if [Group] = "A" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Region"}, {{"Count", each Table.RowCount(_), type number}, {"Average Sales", each List.Average([Sales]), type number}, {"Count2 (is Group A)", each List.Sum([#"Count2 (is Group A)"]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each [Count]/Table.RowCount(Source)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percent", Percentage.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Percent2", each [Count] / [#"Count2 (is Group A)"]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Percent2", Percentage.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"Region", "Count", "Percent", "Count2 (is Group A)", "Percent2", "Average Sales"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Target Hit", each if [Average Sales] > 50 then "Hit" else "Miss"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Target Hit", type text}})
in
    #"Changed Type2"

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur!

I don't have a PBIX file yet but here's the sample data.

Power BI - Groupby Problem

[Ashish_Mathur]

Hi,

Here is the M code i used

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type datetime}, {"Region", type text}, {"Group", type text}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Region"}, {{"Count", each Table.RowCount(_), type number}, {"Average Sales", each List.Average([Sales]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each [Count]/Table.RowCount(Source)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percent", Percentage.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Region", "Count", "Percent", "Average Sales"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Target Hit", each if [Average Sales] > 50 then "Hit" else "Miss"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Target Hit", type text}})
in
    #"Changed Type2"

Hope this helps.

[Anonymous]

@Ashish_Mathur

Thank you so much! This definitely helps! I have additional problem/ requirement though see Calculated Table using Groupby

[Ashish_Mathur]

Hi,

Try this revised code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Timestamp", type datetime}, {"Region", type text}, {"Group", type text}, {"Sales", Int64.Type}}),
    Count2 = Table.AddColumn(Source, "Custom", each if [Group] = "A" then 1 else if [Group] = "B" then 0 else null, type number),
    #"Grouped Rows" = Table.Group(Count2, {"Region"}, {{"Count", each Table.RowCount(_), type number}, {"Average Sales", each List.Average([Sales]), type number}, {"Count1", each List.Sum([Custom]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Percent", each [Count]/Table.RowCount(Source)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Percent1", each [Count1]/[Count]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Percent", Percentage.Type}, {"Percent1", Percentage.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Region", "Count", "Percent", "Average Sales"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Target Hit", each if [Average Sales] > 50 then "Hit" else "Miss"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Target Hit", type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Region", "Count", "Percent", "Count1", "Average Sales", "Target Hit", "Percent1"})
in
    #"Reordered Columns1"

This one has the Percent2 calculation