Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SUM Only Taking Single Value From Column

Hi, I have a value I need to sum from one column, based on filters from two other columns. For example:

 

DeviceIDDateSubtotal
1Dec 810
1Dec 85
1Dec 920
1Dec 930
2Dec 815
2Dec 820
2Dec 910
2Dec 910

 

From this table I'd like to sum the subtotals by day, by device as shown below:

 

Device IDDateTotal
1Dec 815
2Dec 835
1Dec 950
2Dec 920

 

I had help from other members in a previous post here. However, I'm having issues with the solution. It works on small sample data but not on my data. See my sample data here: 

 

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

 

I'm using this measure: 

Total = CALCULATE(SUM(table[subtotal]),ALLEXCEPT(table,table[DeviceID],datetable[Date]))
 
The issue however is that the measure is only returning the first result from my data table, rather than the sum.
 
So in the above example, rather than returning 15 for Device 1 on December 8th, it's returning 10 (the first value for device 1 on december 8th. Or rather than 35 for Device 2 on December 8th it's returning 15.
 
Hopefully this makes sense. Thanks for any help.

 

 

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    Anonymous yes Power Query is where you do the transformation including changing the type of the columns. 

8 Replies

  • az38's avatar
    az38
    Community Champion
    4 years ago

    Hi Anonymous 

    I do not have access to your sources but it seems like your dates actually not the same. Maybe spaces or other invisible symbols. It is a pretty strange filters behaviour

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the reply. I believe this is because the source data was actually date/time, but I've converted that to 'date' data type. This is why I have a date table, I thought that would help with a 1 to many relationship. Perhaps I am wrong. Is there any way to completely 'remove' the time stamp from the dates?

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        Please apply the Date field of DateTable onto the visual to replace the Date field of fact table(ScannerStats)... You can find the details in the attachment.

        Best Regards

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous yes Power Query is where you do the transformation including changing the type of the columns. 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  If you follow the previous post I am advising you to use the following

     

    Measure2 = 
CALCULATE (
    SUM ( ScannerStats[Subtotal] ),
    VALUES ( ScannerStats[DeviceId] ),
    VALUES ( DateTable[Date] )
)

     

    why are you using

    Total = CALCULATE(SUM(table[subtotal]),ALLEXCEPT(table,table[DeviceID],datetable[Date]))

    This will never ever work.

     

    If the axis coming from two different tables respectively ScannerStats[DeviceId] and DateTable.[Date] the measure will work

     

    Also, you are using a

    DateTable = CALENDAR(Date(2021,12,01),Date(2022,10,31))
     
    which does not capture all the date from ScannerStats; instead use CALENDARAUTO()

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      It wasn't the calculation that was wrong, I didn't change the date/time column properly in my data. 

       

      For clarity, here are the two different calculations you mentioned above compared to the original subtotal value. They are all the same

      Once I properly change the date/time column in my data table in power query to just date, both calculations work as intended. Thank you so much for your help with this!