cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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.  

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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