Group by with sum and with average

[flprado87]

Hi all,

I'm working on a transactional tax dataset that has the following situations all together:

- when only one tax applies - there is only one row for that transaction

- when multiple taxes apply - there are many transactions (with the same transaction_id), one for each tax

As to the columns, when multiple taxes apply, some columns will repeat the values (e.g. total) and some columns will contain unique values (e.g. tax charged). 

What i'm trying to do is to group the repeated transaction_id with an average of the columns in which the amounts repeat and with a sum of the amounts that do not repeat 

transaction_id	Total	Subtotal	Tax_amount	jurisdiction_level	jurisdiction_name	Country_code	state_code	Tax_rate	Tax_name
111	100	85	10	country	Canada	CA		10%	GST/HST
111	100	85	5	state	British Columbia	CA	BC	5%	PST
222	100	90	10	country	Canada	CA		10%	GST/HST
333	200	180	20	country	Canada	CA		10%	GST/HST

The result I was expecting would be something like this:

transaction_id	Total	Subtotal	Tax_amount	jurisdiction_level	jurisdiction_name	Country_code	state_code	Tax_rate	Tax_name
111	100	85	15	country;state	Canada;British Columbia	CA	BC	10%;5%	GST/HST;PST
222	100	90	10	country	Canada	CA		10%	GST/HST
333	200	180	20	country	Canada	CA		10%	GST/HST

Note that for the column "total" the group by "transaction_id"  only calculated an average, while for the colum "tax_amount" it was calculated a sum. For the other fields there was a merge of the text. 

Is this something doable in pbi? I have tried many different things and still haven't figured out a way to do this.

Thank you! 

[Anonymous · Accepted solution]

Hi @flprado87 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table 2 = SUMMARIZE('Table',
'Table'[transaction_id],
"Total", AVERAGEX(FILTER(ALL('Table'),'Table'[transaction_id]=MAX('Table'[transaction_id])),'Table'[Total]),
"Subtotal",AVERAGEX(FILTER(ALL('Table'),'Table'[transaction_id]=MAX('Table'[transaction_id])),'Table'[Subtotal]),
"Tax_amount",CALCULATE(SUM('Table'[Tax_amount]),FILTER(ALL('Table'),'Table'[transaction_id]=MAX('Table'[transaction_id]))),
"jurisdiction_level", CONCATENATEX(SUMMARIZE('Table',[transaction_id],'Table'[jurisdiction_level]),[jurisdiction_level],";"),
"jurisdiction_name" ,CONCATENATEX(SUMMARIZE('Table',[transaction_id],'Table'[jurisdiction_name]),[jurisdiction_name],";"),
"Country_code" , CONCATENATEX(SUMMARIZE('Table',[transaction_id],[Country_code]),[Country_code],";"),
"state_code" , CONCATENATEX(SUMMARIZE('Table',[transaction_id],[state_code]),[state_code],";"),
"Tax_rate",CONCATENATEX(SUMMARIZE('Table',[transaction_id],[Tax_rate]),FORMAT([Tax_rate],"Percent"),";"),
"Tax_name",CONCATENATEX(SUMMARIZE('Table',[transaction_id],[Tax_name]),[Tax_name],";"))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[flprado87]

Thank you Alef, where do I add this query? Sorry for the newbie question - I have tried to add this to advanced editor but it didn't work 😞 

[Alef_Ricardo_]

What you're trying to achieve in Power BI involves grouping rows in your transactional tax dataset based on the "transaction_id," and for certain columns, you want to calculate the average for repeating values, sum non-repeating values, and merge text values.

You can accomplish this using DAX measures and Power Query. Here's a step-by-step approach:

**Step 1: Power Query Transformation**

1. Create a unique identifier for each unique combination of "transaction_id" and "Tax_name" because you want to aggregate rows based on "Tax_name" but retain "transaction_id."
2. In Power Query, add a custom column that concatenates the "Tax_name" values using a delimiter (e.g., a semicolon) for each unique "transaction_id."

Your Power Query code might look something like this (assuming your dataset is named "TaxData"):

```M
let
Source = TaxData,
// Create a unique identifier for each combination of "transaction_id" and "Tax_name"
AddCustom = Table.AddColumn(Source, "TransactionTaxKey", each [transaction_id] & " - " & [Tax_name]),
// Group by "TransactionTaxKey" and aggregate other columns
Grouped = Table.Group(AddCustom, {"TransactionTaxKey"}, {
{"Total", each List.Average([Total]), type number},
{"Subtotal", each List.Average([Subtotal]), type number},
{"Tax_amount", each List.Sum([Tax_amount]), type number},
{"jurisdiction_level", each [jurisdiction_level]{0}},
{"jurisdiction_name", each [jurisdiction_name]{0}},
{"Country_code", each [Country_code]{0}},
{"state_code", each [state_code]{0}},
{"Tax_rate", each [Tax_rate]{0}},
{"Tax_name", each Text.Combine([Tax_name], ";")}
}),
// Remove temporary column and keep the original columns
Expanded = Table.ExpandTableColumn(Grouped, "TransactionTaxKey", {"Total", "Subtotal", "Tax_amount", "jurisdiction_level", "jurisdiction_name", "Country_code", "state_code", "Tax_rate", "Tax_name"})
in
Expanded
```

**Step 2: Create Measures for Averages**

1. Create measures that calculate the average of "Total" and "Subtotal" based on the new grouping.

For "Average Total":
```DAX
Average Total = AVERAGE(TaxData[Total])
```

For "Average Subtotal":
```DAX
Average Subtotal = AVERAGE(TaxData[Subtotal])
```

**Step 3: Create Visualizations**

Now, you can create visuals in Power BI using the measures "Average Total" and "Average Subtotal," as well as the columns you transformed in Power Query.

This approach should give you the desired result where you have grouped rows based on "transaction_id" and performed the necessary calculations for the "Total" and "Subtotal" columns. The text values in the other columns are concatenated based on the "Tax_name" for each unique "transaction_id."