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 would like to work out is the rate of products returned for each reason and category. And this is calculated as the number of return divided by the total of products out by a particular set of variables (categories). 

 

I need to get a measure/column that calculates this total product out by the set categories

 

e.g. In the sample below where market = abc, cat1=adult, cat2=womenswear, cat3=accessories and cat4=belt, the total is 153 (90+11+26+26).

 

At the moment, if i calculate in-rate, they come up as 100% which is wrong.

 

Sample data output

RowMarketCat1Cat2Cat3Cat4Reas1Reas2OutReturnIn Rate
1abcAdultWomenswearAccessoriesBelts  90  
2abcAdultWomenswearAccessoriesBeltsFitBig1111100%
3abcAdultWomenswearAccessoriesBeltsOtherUnknown2626100%
4abcAdultWomenswearAccessoriesBeltsStylingUnsuitable2626100%

 

 

Desired data output

RowMarketCat1Cat2Cat3Var4Reas1Reas2OutReturnIn Rate
1abcAdultWomenswearAccessoriesBelts  90  
2abcAdultWomenswearAccessoriesBeltsFitBig11117%
3abcAdultWomenswearAccessoriesBeltsOtherUnknown262617%
4abcAdultWomenswearAccessoriesBeltsStylingUnsuitable262617%

 

The total in rate for cat4=belts is 43% - total in (63)/total out (153)

 

Hope this all makes sense and many thanks for your help!

 

 

afk

Need Help
  • Baskar's avatar
    Baskar
    7 years ago

    Cool try this 

     

    Percentage = SUM(Com1[Return])/CALCULATE(SUM(Com1[Out]),ALLEXCEPT(Com1,Com1[Cat4]))
     

4 Replies

  • 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]))