cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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 NumberSKUQtySKU TAXTOTAL TAX PO
1001AAA3$3$10
1001BBB2$2$10
1001CCC4$4$10
1001DDD1$1$10
1001EEE2$0$10
1001FFF1$0$10
1002AAA2$2$11
1002BBB4$4$11
1002CCC5$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
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])

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User IV
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])

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User IV
Super User IV

@IB-Dee , You can try all expect

 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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