cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Total All Items on Transaction that include a Specified Value

I have a table below.  I want to write a dax expression to get the total of all sales that included "Alcohol" on the ticket.  Any ideas?

 

Annotation 2019-03-26 140620.png

I am trying to achieve the $18.50 value.  Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Try this measure:

 

Measure = 
VAR __table = SUMMARIZE('Table14',[Transaction ID],"__departments",CONCATENATEX(VALUES(Table14[Department]),[Department],","),"__sum",SUM([Net Total]))
VAR __table1 = ADDCOLUMNS(__table,"__alcohol",CONTAINSSTRING([__departments],"Alcohol"))
RETURN
SUMX(FILTER(__table1,[__alcohol]),[__sum])

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

@ 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

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Try this measure:

 

Measure = 
VAR __table = SUMMARIZE('Table14',[Transaction ID],"__departments",CONCATENATEX(VALUES(Table14[Department]),[Department],","),"__sum",SUM([Net Total]))
VAR __table1 = ADDCOLUMNS(__table,"__alcohol",CONTAINSSTRING([__departments],"Alcohol"))
RETURN
SUMX(FILTER(__table1,[__alcohol]),[__sum])

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

@ 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

Highlighted

This worked.  Thank you!!

 

I am new to Power BI so I am not exactly sure what this expression is actually doing but it does seem to take a while to calculate in the visual.  My table that it is working with has about 600,000 rows.  

Highlighted

So, by way of explanation:

 

Measure =
VAR __table = SUMMARIZE('Table14',[Transaction ID],"__departments",CONCATENATEX(VALUES(Table14[Department]),[Department],","),"__sum",SUM([Net Total]))
VAR __table1 = ADDCOLUMNS(__table,"__alcohol",CONTAINSSTRING([__departments],"Alcohol"))
RETURN
SUMX(FILTER(__table1,[__alcohol]),[__sum])

 

The first line summarizes your table by transaction id, so think of "grouping", you end up with one line per transaction id. Added to this are 2 aggregation columns. The "_departments" column concatenates all of the values in your Department column for that particular transaction. The "_sum" column sums up your Net Total column.

 

The next line adds a column to this table that is simply a true/false of whether the _departments column contains the word "Alcohol". Since _departments concatenates all of the values for a transaction, then if a transaction includes alcohol, this will be true.

 

The final line returns a summation of the __sum column for all of the rows in the summarized table where __alcohol is TRUE.


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

@ 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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors