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 ID Day Subtotal Device01 December 1st 10 Device01 December 1st 30 Device01 December 1st 15 Device02 December 1st 10 Device02 ...
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

     

     

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.