Forum Discussion
Total All Items on Transaction that include a Specified Value
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])
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.
Community ChampionSo, 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.