cancel
Showing results for
Did you mean:
Helper I

## SUMX Total based on a Column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

@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])``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

5 REPLIES 5
Super User IV

@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])``````

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Super User IV

@IB-Dee , You can try all expect

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

Proud to be a Super User!

Helper I

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

Helper I

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

Super User IV

@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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors