Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sum Values Based on Multiple Entries from Multiple Columns

Hi, I have a table as shown below:

 

Device IDDaySubtotal
Device01December 1st10
Device01December 1st30
Device01December 1st15
Device02December 1st10
Device02December 1st5
Device01December 2nd10
Device01December 2nd20
Device02December 2nd10
Device02December 2nd30
Device02December 2nd5

 

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 IDDayTotal
Device01December 1st55
Device02December 1st15
Device01December 2nd30
Device02December 2nd45

 

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,

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous you can use a measure like this

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

  • smpa01's avatar
    smpa01
    4 years ago

    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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous you can use a measure like this

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        smpa01
        Community Champion
        4 years ago

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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

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

      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.

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    To your matrix visual, drag Device ID and Day to the Row well.  Write this measure

    Total = sum(Data[Subtotal])

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?