Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
7 years ago
Solved

Sum by multiple columns

Hi,   I have some stock data that shows products sent out and products returned, along with return reasons (row 2,3,4). I also have numbers for those sent out but not returned (row 1).    What I ...
Need Help
  • Baskar's avatar
    Baskar
    7 years ago

    Cool try this 

     

    Percentage = SUM(Com1[Return])/CALCULATE(SUM(Com1[Out]),ALLEXCEPT(Com1,Com1[Cat4]))
     
Baskar's avatar
Baskar
Resident Rockstar
7 years ago

Hey can you please try this :

 

Measure 2 = SUM(Com1[Return])/CALCULATE(SUM(Com1[Out]),ALL(Com1))
 
bo_afk's avatar
bo_afk
Post Patron
7 years ago

Thanks for your suggestion Baskar.

 

But it doesn't seem to work in my data. In the sample data set it does, but i actually have more values for each of the categories which could be the reason why it's not working.

 

Please see below:

 

Can you advise further?

  • Baskar's avatar
    Baskar
    Resident Rockstar
    7 years ago

    Cool try this 

     

    Percentage = SUM(Com1[Return])/CALCULATE(SUM(Com1[Out]),ALLEXCEPT(Com1,Com1[Cat4]))
     
    • bo_afk's avatar
      bo_afk
      Post Patron
      7 years ago

      Thanks Baskar, this has worked! :smileyvery-happy:

       

      However I made a small change in the "allexcept" part where I included all the cat columns as opposed to just cat4. I assume this allows the correct calculations when grouped by any of the categories. Do correct me if I'm wrong though.