cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
girinpanda Member
Member

Create Calculated Table using Groupby

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:Power BI 1.JPG

 

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) 

 

Power BI 2.JPG

 

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!

10 REPLIES 10
Gazzer Member
Member

Re: Create Calculated Table using Groupby

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.

 

 

image.png

 

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.

 

girinpanda Member
Member

Re: Create Calculated Table using Groupby

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?

GroupbyPowerBI.JPG

 

Gazzer Member
Member

Re: Create Calculated Table using Groupby

Hi @girinpanda

 

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. 

Highlighted
girinpanda Member
Member

Re: Create Calculated Table using Groupby

@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)powerbi.JPG

 

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

Super User
Super User

Re: Create Calculated Table using Groupby

Hi,

 

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

girinpanda Member
Member

Re: Create Calculated Table using Groupby

Hi @Ashish_Mathur!

 

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

Power BI - Groupby Problem

 

Super User
Super User

Re: Create Calculated Table using Groupby

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.

 

Untitled.png

girinpanda Member
Member

Re: Create Calculated Table using Groupby

@Ashish_Mathur

 

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

Super User
Super User

Re: Create Calculated Table using Groupby

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