Forum Discussion
Sum Values Based on Multiple Entries from Multiple Columns
Hi, I have a table as shown below:
| Device ID | Day | Subtotal |
| Device01 | December 1st | 10 |
| Device01 | December 1st | 30 |
| Device01 | December 1st | 15 |
| Device02 | December 1st | 10 |
| Device02 | December 1st | 5 |
| Device01 | December 2nd | 10 |
| Device01 | December 2nd | 20 |
| Device02 | December 2nd | 10 |
| Device02 | December 2nd | 30 |
| Device02 | December 2nd | 5 |
I need to add the subtotals based on device ID and day. The dates and subtotals will update daily to include the most recent data.
So my desired end result is as follows:
| Device ID | Day | Total |
| Device01 | December 1st | 55 |
| Device02 | December 1st | 15 |
| Device01 | December 2nd | 30 |
| Device02 | December 2nd | 45 |
Then I need to make a stacked bar chart per device with total as the y axis and date as the x axis (not sure if this changes the approach to the solution)
I appreciate any help!
Thanks,
Anonymous you can use a measure like this
Measure= CALCULATE(SUM(tbl[Subtotal]), ALLEXCEPT(tbl,tbl[Device ID],tbl[Day]))
Anonymous when all the axis comes form Scanner Stats
Measure = CALCULATE ( SUM ( ScannerStats[Subtotal] ), ALLEXCEPT ( ScannerStats, ScannerStats[DeviceId], ScannerStats[Date] ) )when axis come from different tables
Measure2 = CALCULATE ( SUM ( ScannerStats[Subtotal] ), VALUES ( ScannerStats[DeviceId] ), VALUES ( DateTable[Date] ) )pbix is attached
13 Replies
Anonymous you can use a measure like this
Measure= CALCULATE(SUM(tbl[Subtotal]), ALLEXCEPT(tbl,tbl[Device ID],tbl[Day]))
smpa01 Thanks for the attachment. I see that it works on yours so I marked it as the correct solution, but I still see this on mine:
The sum on the first row is correct, but then when it breaks it down by day, it's showing the first entry in the data rather than summing all the data per day:
Is this possibly because I'm using a date table? I've tried removing the date table and using the date from the original table but that doesn't work either. This is my measure:
I really appreciate your help on this.
Anonymous please prepare a sample pbix, upload in g/1 drive and please share the link
Anonymous when all the axis comes form Scanner Stats
Measure = CALCULATE ( SUM ( ScannerStats[Subtotal] ), ALLEXCEPT ( ScannerStats, ScannerStats[DeviceId], ScannerStats[Date] ) )when axis come from different tables
Measure2 = CALCULATE ( SUM ( ScannerStats[Subtotal] ), VALUES ( ScannerStats[DeviceId] ), VALUES ( DateTable[Date] ) )pbix is attached
Yes that is the same that I'm getting in my original model. The issue is that the measure isn't actually showing the sum by day.
For example, December 1st shows 29 under Measure 2 in "Axis from different tables". When the value I'm acutally looking for is 440 for December 1st. As you can see below 29 is just the first value in the tableThanks.
Hi,
To your matrix visual, drag Device ID and Day to the Row well. Write this measure
Total = sum(Data[Subtotal])
Hope this helps.
smpa01 thanks for the response! Unfortunately it's still showing it by individual record per day rather than sum per day. The chart below is filtered by a single device ID.
Any other suggestions?
Anonymous check attached
