Forum Discussion
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?
I am trying to achieve the $18.50 value. Thanks!
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])
4 Replies
- Greg_Deckler
Community Champion
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])- 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.
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.
