Question for Sum with specific conditions in Power BI

[pianobleman7]

Item	Mother Item	Sales
A		1
B	A	2
B	A	1
C	D	3
D		4
E		2
E		4
Total		17

I have former sales table. 

Some of Item has a mother item(B's mother item is "A", "C"'s mother items is"D", and "E" doesn't have a mother item.) I would like to sum up the sales of each item like below.

A and B have the value samely summing "A" sales Sum + "B" sales Sum (1+2+1=4)

C and B have the value samely summing "C" sales Sum + "D" sales Sum(3+3=6)

E  has sales 2pcs and 4pcs all together 6pcs. is it possible?

Total sales should be 17 as below.

Item	Sales Sum
A	4
B	4
C	7
D	7
E	6
Total	17

[v-xinruzhu-msft · Accepted solution]

Hi @pianobleman7 

You can refer to the following measure

Sum = var a=SUMMARIZE(FILTER(ALL('Table'),[Item]=SELECTEDVALUE('Table'[Item])),[Item])
var b=SUMMARIZE(FILTER(ALL('Table'),[Item]=SELECTEDVALUE('Table'[Item])),[Blank Item])
return SUMX(FILTER(ALL('Table'),OR([Item] in SUMMARIZE(FILTER(ALL('Table'),OR('Table'[Item]=SELECTEDVALUE('Table'[Item]),[Blank Item]=SELECTEDVALUE('Table'[Item]))),[Item]),[Item] in UNION(a,b))),[Sales])

Output

Best Regards!

Yolo Zhu

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

[lbendlin]

According to your sample data the sum for A should be 3 ?

[pianobleman7]

I've changed "blank item" terms to relative item.

I want to sum each item's Sales both original item and relative item.

so relative item and original item could have same sum value as example.

[lbendlin]

The second B item does not have a related A item.

[pianobleman7]

sorry my bad.

I've changed it.

[v-xinruzhu-msft]

Hi 

[pianobleman7]

!?!?!?!?

[v-xinruzhu-msft · Accepted solution]

Hi @pianobleman7 

You can refer to the following measure

Sum = var a=SUMMARIZE(FILTER(ALL('Table'),[Item]=SELECTEDVALUE('Table'[Item])),[Item])
var b=SUMMARIZE(FILTER(ALL('Table'),[Item]=SELECTEDVALUE('Table'[Item])),[Blank Item])
return SUMX(FILTER(ALL('Table'),OR([Item] in SUMMARIZE(FILTER(ALL('Table'),OR('Table'[Item]=SELECTEDVALUE('Table'[Item]),[Blank Item]=SELECTEDVALUE('Table'[Item]))),[Item]),[Item] in UNION(a,b))),[Sales])

Output

Best Regards!

Yolo Zhu

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

[pianobleman7]

Thanks for your reply.

it works very well. however, could I have total sum ?17 at the bottom of table?

[v-xinruzhu-msft · Accepted solution]

Hi @pianobleman7 

Just create a new measure

Measure 2 = SUMX(VALUES('Table'[Item]),[Sum])

and put it to the table, and the total should be 28.

Best Regards!

Yolo Zhu