DAX: Family Total next to each Item Total

[im2fast4u]

I have several tables in a relational DB format in PowerPivot. One table contains the sales for each item. Another Master table contains a list of families.
I would like to create a Pivot table column (PowerPivot) with an expression that shows the SUM OF SALES BY FAMILY for each row of item.
What kind of PowerPivot DAX expression will get me the result in colum 4 (Family Sales)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Family[/TD]
[TD]ItemSales[/TD]
[TD]FamilySales[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[TD]$900[/TD]
[/TR]
[TR]
[TD]252[/TD]
[TD]2[/TD]
[TD]$400[/TD]
[TD]$1700[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]2[/TD]
[TD]$800[/TD]
[TD]$1700[/TD]
[/TR]
[TR]
[TD]372[/TD]
[TD]1[/TD]
[TD]$300[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD]324[/TD]
[TD]3[/TD]
[TD]$700[/TD]
[TD]$900[/TD]
[/TR]
[TR]
[TD]762[/TD]
[TD]2[/TD]
[TD]$500[/TD]
[TD]$1700[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

I will be speaking in terms of measures (aka calculated fields)... a calc column would be different.

FamilySales := CALCULATE(SUM(SalesTable[ItemSales]), ALLEXCEPT(SalesTable, SalesTable[Family]))

Which is basically, gimme the sum of itemsales, but strip away ALL filter context... except the Family column.

Depending on pivot table / slicers, that may remove too many filters. Could also try something like:
FamilySales := CALCULATE(SUM(SalesTable[ItemSales]), ALL(SalesTable[Item]))

Which will only strip away filter context for the Item id column.

 

[Amit Jain]

Hi,

Apply Pivot table as per below. This will gives you sum of Item sales and Family sales by row in Pand Q column.

Row Lables - Family
Values - Sum of item sales & Sum of Family sales
Column Lables - Item

Note - In Column lables Values will reflect auto you need to put Item column up.

 

[im2fast4u]

I will be speaking in terms of measures (aka calculated fields)... a calc column would be different.

FamilySales := CALCULATE(SUM(SalesTable[ItemSales]), ALLEXCEPT(SalesTable, SalesTable[Family]))

Which is basically, gimme the sum of itemsales, but strip away ALL filter context... except the Family column.

Depending on pivot table / slicers, that may remove too many filters. Could also try something like:
FamilySales := CALCULATE(SUM(SalesTable[ItemSales]), ALL(SalesTable[Item]))

Which will only strip away filter context for the Item id column.

Thank you very much Scottsen for a very quick feedback! The solution that you provided would surely work if my data was arranged hierarchically in rows like 'ItemFamily>Item'. But I would like to develop a flat looking (kind of iSeries) report where the first column is Item, second column is ItemFamily, third column is SumSales and fourth column is FamilySales. All this is flat and no hierarchy like it can be done in a Pivot table, just like the table I had in the original post.

Thank you very much again!

 

[scottsen]

In the power pivot window when you insert a pivot table, one of the options is to insert a "flattened pivot table". That should give what you want.