cancel
Showing results for
Did you mean:
Highlighted
Post Patron

## 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

 Row Market Cat1 Cat2 Cat3 Cat4 Reas1 Reas2 Out Return In Rate 1 abc Adult Womenswear Accessories Belts 90 2 abc Adult Womenswear Accessories Belts Fit Big 11 11 100% 3 abc Adult Womenswear Accessories Belts Other Unknown 26 26 100% 4 abc Adult Womenswear Accessories Belts Styling Unsuitable 26 26 100%

Desired data output

 Row Market Cat1 Cat2 Cat3 Var4 Reas1 Reas2 Out Return In Rate 1 abc Adult Womenswear Accessories Belts 90 2 abc Adult Womenswear Accessories Belts Fit Big 11 11 7% 3 abc Adult Womenswear Accessories Belts Other Unknown 26 26 17% 4 abc Adult Womenswear Accessories Belts Styling Unsuitable 26 26 17%

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar

Cool try this

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

4 REPLIES 4
Highlighted
Resident Rockstar

Hey can you please try this :

Measure 2 = SUM(Com1[Return])/CALCULATE(SUM(Com1[Out]),ALL(Com1))

Highlighted
Post Patron

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.

Highlighted
Resident Rockstar

Cool try this

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

Highlighted
Post Patron

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors