Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.