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