Total All Items on Transaction that include a Specified Value

[Anonymous]

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?

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

[kjmullen]

@Greg_Deckler Apologies for reviving such an old thread, but I haven't been able to find a more recent example and this is almost exactly what I need.  Is there a way to use department as a slicer and remove the hard-coded department reference?  I've tried using SELECTEDVALUE, but that is not retuning the right values.  Ideally, I'd want the slicer to be a multi select, but I can't even get a single value to return the sum I expect.

[Greg_Deckler · Accepted solution]

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

[Anonymous]

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.  

[Greg_Deckler]

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.