Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
2 years ago
Solved

Sum - Calculated Column

Hi good day,

Can anyone help me on my calculated column on how to arrived on my desired outcome.

Current Table

 

Desired Outcome adding calculated column

 

Thank you

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please try something like below (the picture and the attached pbix file).

     

     

     

    desired outcome CC = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            data[date],
            data[location],
            data[trade],
            data[week_no],
            data[count]
        ),
        data[date] = EARLIER ( data[date] )
            && data[location] = EARLIER ( data[location] )
    ),
    data[count]
)

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please try something like below (the picture and the attached pbix file).

     

     

     

    desired outcome CC = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            data[date],
            data[location],
            data[trade],
            data[week_no],
            data[count]
        ),
        data[date] = EARLIER ( data[date] )
            && data[location] = EARLIER ( data[location] )
    ),
    data[count]
)

     

    • dvraan's avatar
      dvraan
      Frequent Visitor
      1 year ago
      CALCULATE(SUMX(DISTINCT(data[count]), data[count]), ALLEXCEPT(data, data[date], data[location]))

       

      But I suggest Remove duplicate in power query 

       

      CALCULATE(SUM(data[count]), ALLEXCEPT(data, data[date], data[location]))
  • dvraan's avatar
    dvraan
    Frequent Visitor
    1 year ago
    CALCULATE(SUMX(DISTINCT(data[count]), data[count]), ALLEXCEPT(data, data[date], data[location]))

     

    But i suggest to use Remove duplicate in power query 

     

    CALCULATE(SUM(data[count]), ALLEXCEPT(data, data[date], data[location]))