Forum Discussion

Pal03's avatar
Pal03
Frequent Visitor
8 years ago
Solved

Sum over partition by

Hello everyone , I am trying to achieve this . I have a table with Date , account , segment , revenue and sector. I am trying to get 

total revenue based on Date , Account , segment only.

 

My total revenue changes  everytime there is a new date ,account , segment combination  (ignoring the sector) Please help

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi Pal03

     

    Here is how you can do it with a calculated column

     

    Total Revenue = 
    CALCULATE(
        SUM('Table1'[Revenue]),
        FILTER(
            'Table1',
            'Table1'[Date] = EARLIER('Table1'[Date]) &&
            'Table1'[Account] = EARLIER('Table1'[Account]) &&
            'Table1'[Segment] = EARLIER('Table1'[Segment]) 
            )   
)

7 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Pal03

     

    Here is how you can do it with a calculated column

     

    Total Revenue = 
    CALCULATE(
        SUM('Table1'[Revenue]),
        FILTER(
            'Table1',
            'Table1'[Date] = EARLIER('Table1'[Date]) &&
            'Table1'[Account] = EARLIER('Table1'[Account]) &&
            'Table1'[Segment] = EARLIER('Table1'[Segment]) 
            )   
)

    • Pal03's avatar
      Pal03
      Frequent Visitor
      8 years ago

      Thanks a ton . It works !!

      • Pal03's avatar
        Pal03
        Frequent Visitor
        8 years ago

        Actually quite excited that it works in power BI. Once again thank you :)

    • mleepin's avatar
      mleepin
      Helper I
      8 years ago
      Just wondering - Would it be possible to do this using power query / M?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks a lot!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Can we do the same in Power query?