SUMX Total based on a Column

[IB-Dee]

Hi All

I'm relatively new to PowerBI and struggling to find an answer to the problem below. I'm looking for a Measure to calculate the last column (TOTAL TAX FOR PO), which would be the total of the SKU TAX column for each PO Number in the 1st Column.

Example: TOTAL TAX PO for PO Number 1001 = $3+$2+$4+$1+$0+$0=$10. 

Note: "SKU TAX"  is a SUMX Measure combining 2 columns from two different tables.

PO Number	SKU	Qty	SKU TAX	TOTAL TAX PO
1001	AAA	3	$3	$10
1001	BBB	2	$2	$10
1001	CCC	4	$4	$10
1001	DDD	1	$1	$10
1001	EEE	2	$0	$10
1001	FFF	1	$0	$10
1002	AAA	2	$2	$11
1002	BBB	4	$4	$11
1002	CCC	5	$5	$11

I tried with Calculate with a filter on PO Number, but it gives me the same result as my SKU TAX Measure.

Any help would be greatly appreciated.

Thanks

[amitchandak]

@IB-Dee , You can try all expect

calculate([SKU TAX], allexcept(Table,[PO Number]))

[IB-Dee]

Hey @amitchandak 

Thanks for taking the time to respond.

I also tried the formula, unfortunately, it returned the sum of tax for all the PO Number, not the sum of tax for each PO. Not sure if I did something wrong.

Thanks

[Greg_Deckler · Accepted solution]

@IB-Dee - Perhaps try something like:

TOTAL TAX PO Measure = 
  VAR __PO = MAX('Table'[PO Number])
RETURN
  SUMX(FILTER(ALL('Table'),[PO Number] = __PO),[SKU TAX])

[IB-Dee]

Hey @Greg_Deckler 

Thanks for taking the time to respond, much appreciated.

I'll need to go back to try to understand how you got there with this formula but it worked!

Cheers

[Greg_Deckler]

@IB-Dee - Sure, first line gets the current value of the PO Number column for the current row. The RETURN statement uses ALL to break out of the current context of the individual row and then you use FILTER to filter it back down to just the rows in the table where the PO Number matches the __PO variable value. Then you just sum up the column values.