Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Cool try this
Hey can you please try this :
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?
Cool try this
Thanks @Baskar, this has worked!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |