topic Aggregate sum of parent to be shown in table when lower level is visible, keep other filters in DAX Commands and Tips

Aggregate sum of parent to be shown in table when lower level is visible, keep other filters

Anonymous — Thu, 14 May 2020 07:31:13 GMT

This is something that has been asken in various ways but cannot figure it out. I would like to aggregate by higher level in dimension table than I am showing in reporting Table. I am showing Team but I would like to show higher level (Business line) to which Team is tied to. Multiple teams can be tied to a Business line.

Tables

-Dimension: Country table which contains basically country.

-Dimension: Organization table where lowest level is Team and highest Business line. (i.e. each team belongs to business line)

-Dimension: Date table

-Fact table -> "Sales" Note: If I filter only Country=X, I do not want to see other countries rows filled with "aggregate of business line" but otherwise empty.

I cannot post the table for some reason. I try to reply that one.

Re: Aggregate sum of parent to be shown in table when lower level is visible, keep other filters

Anonymous — Thu, 14 May 2020 08:48:16 GMT

Example table. Please help to calculate the last column starting with "how to calculate". Basically that is sum of "Value of the row" per each unique value in '(Organization table) Business line' column.

I would like to see the below results of "aggregate" on each team's row even when Business line column is not used as a column in reporting table. Having Business line below is just to show example but in use cases Business line might not be in the table.

(Country table) Country	(Organization table) Business line	(Organization table) Team	(Date) Month	(Sales table) Value of the row	How to calculate -> (Sales table) Aggregate Sum of business line per 'Value of the row' column
X	Toys	1	May 2020	100	300
X	Toys	2	May 2020	200	300
X	Cars	3	May 2020	300	300
Y	Horses	4	May 2020	400	900
Y	Horses	5	May 2020	500	900
Y	Pigs	6	May 2020	600	600

Re: Aggregate sum of parent to be shown in table when lower level is visible, keep other filters

Anonymous — Thu, 14 May 2020 09:39:14 GMT

I finally found the solution by going through multiple quite similar but not exact cases here at the Community. I solved with this:

Measure = SUMX ( VALUES ( Organization[BusinessLine] ), Calculate([SalesSumMeasure];ALL(Organization[Team]))