DAX SUM function per group

[martijnvanderveldt]

Hi Guys!

I am working in Power BI Desktop (Similar as Power Query) which uses DAX. I have 3 columns:, Response ID, Brand and Volume. Since I have around 50 brands per ID, the Response ID is shown 50 times. Now I want to summarize the total volume PER Response ID in a separate column, but when I use a SUM function, it sums all response ID's up while I want to sum the volume based on response ID (Thus combining the volume of the 50 brands into one sum showing for each ID, thus 50 times the same value per ID). Simply grouping wouldn't work since I later want to calculate the market share per ID.


Now I am new in DAX so I haven't found the right formula yet.

Anyone any idea how to pull this one off?

 

[Chrisdontm]

Have you tried the SUMIF function, it will allow you to insert a criteria...?

 

[scottsen]

I feel like I am missing something.

Given, just the simple measure:
Total Volume := SUM(MyTable[Volume])

If you work with [Total Volume] in values and Response Id (and/or Brand, really) on rows... does that give you what you want, or no?

 

[martijnvanderveldt]

Unfortunately, both options don't work. Power BI Desktop is not the most DAX friendly software yet, but its getting there. If I use the simple SUM function, it just sums all volumes together as a total, but does not do so separately for the brands. It doesn't allow to use the SUMIF function either unfortunately. That's why I am looking for a separate formula which might be in there which can do the trick.

 

[ImkeF]

Why not use M then?:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Volume", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"ResponseID"},#"Changed Type",{"ResponseID"},"NewColumn"),
#"Aggregated NewColumn" = Table.AggregateTableColumn(#"Merged Queries", "NewColumn", {{"Volume", List.Sum, "SumPerResponseID"}})
in
#"Aggregated NewColumn"

This code will add the desired column (as far as I've understood it) into your source table.

 

[scottsen]

Might be best to do what Imke suggests in your case, but ... if you create a grid, with Brand on rows, and the Total in Values... I still think you get what you want.