Sum Values Based on Multiple Entries from Multiple Columns

[Anonymous]

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,

[smpa01 · Accepted solution]

@Anonymous you can use a measure like this

Measure= CALCULATE(SUM(tbl[Subtotal]), ALLEXCEPT(tbl,tbl[Device ID],tbl[Day]))

[smpa01 · Accepted solution]

@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

[Anonymous]

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 table

Thanks.

[Anonymous]

@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?

[smpa01]

@Anonymous  check attached

[Ashish_Mathur]

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 · Accepted solution]

@Anonymous you can use a measure like this

Measure= CALCULATE(SUM(tbl[Subtotal]), ALLEXCEPT(tbl,tbl[Device ID],tbl[Day]))

[Anonymous]

@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.

[smpa01]

@Anonymous  please prepare a sample pbix, upload in g/1 drive and please share the link

[Anonymous]

Sample pbix here:

https://drive.google.com/file/d/1avV3T8nW6Maluxg3Piu3f6TJ5IS6WrWt/view?usp=sharing

Thank you.

[smpa01]

Access denied

[Anonymous]

Apologies, link here with access:

https://drive.google.com/file/d/1avV3T8nW6Maluxg3Piu3f6TJ5IS6WrWt/view?usp=sharing

[smpa01]

@Anonymous  just to be clear, you want to bring axis from two different tables?

Device Id from ScannerStats and Date from DateTable?

[Anonymous]

Yes that is correct. 

I do have a date field in ScannerStats as well which didn't seem to work with your provided solution, but if it works for you I wouldn't mind using it