Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
@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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |