Category based Cumulative total and ABC segmentation

[Rahul109]

Hi guys, I need to create a measure that calculates cumulative total and then put them into ABC category.

Category- Country and material number are the column coming from Product Dimention table and TO 3M is coming from Finance table. I need to arrange the material number into Desc order on the basis of TO 3M but it should be grouped by Category-Country. For example if you see row 10 of table , material no 281's TO is highest in the DEODORANT & FRAGRANCE_Nepal but not for other category.

After that, In column - % of parent row total, we are calculating % of material number turn over with respect to its parent category-country turnover total same as we do in excel pivot table.

Then i need to calculate Cumulative total which is of material number belonging to same category-country field.

I need to do this all on measure level because the calculation id dynamic. you can consider this as 1 month data and for that month the turnover is this and when the customer changes the month all this will also change.

This is the Input table which is not in desired order. Sorting should be done in material number based on TO 3M  inside the category-Country. Different category-Country can be arranged in any order.

Category - Country	Material Number	Sum of TO 3M
DEODORANT & FRAGRANCE_India	121	202289
DEODORANT & FRAGRANCE_India	101	121921
DEODORANT & FRAGRANCE_Bhutan	17	123955
DEODORANT & FRAGRANCE_Bhutan	98	122866
DEODORANT & FRAGRANCE_Bhutan	21	97119
DEODORANT & FRAGRANCE_Bhutan	541	96482
DEODORANT & FRAGRANCE_India	987	67414
DEODORANT & FRAGRANCE_India	234	66881
DEODORANT & FRAGRANCE_Nepal	100	304891
DEODORANT & FRAGRANCE_Nepal	177	290550
DEODORANT & FRAGRANCE_India	451	97020
DEODORANT & FRAGRANCE_India	397	87978
DEODORANT & FRAGRANCE_India	12	83662
DEODORANT & FRAGRANCE_India	99	68147
DEODORANT & FRAGRANCE_Nepal	401	134558
DEODORANT & FRAGRANCE_Bhutan	51	222082
DEODORANT & FRAGRANCE_Bhutan	23	145511
DEODORANT & FRAGRANCE_Bhutan	500	135259
DEODORANT & FRAGRANCE_Nepal	86	287092
DEODORANT & FRAGRANCE_Nepal	59	199299
DEODORANT & FRAGRANCE_Nepal	361	182553
DEODORANT & FRAGRANCE_Nepal	381	156836

For ABC bucket , logic is 

A	0%	A
B	80%	B
C	95%	C
D	99%	D

 Please can some one help on this.

output file : 

Category - Country	Material Number	Sum of TO 3M	Sum of TO 3M2	Cumulative	ABC Bucket
DEODORANT & FRAGRANCE_India	121	202289	25.44%	25.44%	A
DEODORANT & FRAGRANCE_India	101	121921	15.33%	40.77%	A
DEODORANT & FRAGRANCE_India	451	97020	8.48%	49.24%	A
DEODORANT & FRAGRANCE_India	397	87978	11.06%	60.30%	A
DEODORANT & FRAGRANCE_India	12	83662	8.57%	68.87%	A
DEODORANT & FRAGRANCE_India	99	68147	12.20%	81.07%	B
DEODORANT & FRAGRANCE_India	987	67414	8.41%	89.48%	B
DEODORANT & FRAGRANCE_India	234	66881	10.52%	100.00%	D
DEODORANT & FRAGRANCE_Nepal	100	304891	19.60%	19.60%	A
DEODORANT & FRAGRANCE_Nepal	177	290550	18.68%	38.27%	A
DEODORANT & FRAGRANCE_Nepal	86	287092	18.45%	56.73%	A
DEODORANT & FRAGRANCE_Nepal	59	199299	12.81%	69.54%	A
DEODORANT & FRAGRANCE_Nepal	361	182553	11.73%	81.27%	B
DEODORANT & FRAGRANCE_Nepal	381	156836	10.08%	91.35%	B
DEODORANT & FRAGRANCE_Nepal	401	134558	8.65%	100.00%	D
DEODORANT & FRAGRANCE_Bhutan	51	222082	23.54%	23.54%	A
DEODORANT & FRAGRANCE_Bhutan	23	145511	15.43%	38.97%	A
DEODORANT & FRAGRANCE_Bhutan	500	135259	14.34%	53.31%	A
DEODORANT & FRAGRANCE_Bhutan	17	123955	13.14%	66.45%	A
DEODORANT & FRAGRANCE_Bhutan	98	122866	13.03%	79.48%	A
DEODORANT & FRAGRANCE_Bhutan	21	97119	10.30%	89.77%	B
DEODORANT & FRAGRANCE_Bhutan	541	96482	10.23%	100.00%	D

[Jihwan_Kim]

Hi, @Rahul109 

I am not sure how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below.

I suggest having a grouping table like below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/l8adu2ppqjvkvmk/rahul.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Rahul109]

Hi, @Jihwan_Kim thank you for replying.

i have updated the question please go through it. Actually i need to sort the data first then i need a measure which performs all the calculation from column 4 to 6 then give column 6 as an output. 

[Jihwan_Kim · Accepted solution]

Hi, @Rahul109 

Thank you for your feedback.

Please check the below picture and the link down below.

https://www.dropbox.com/s/l8adu2ppqjvkvmk/rahul.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Rahul109]

Hi @Jihwan_Kim I have updated the sample dataset. In earlier data set Material codes were in Desc order but in my real data set they are not. Your logic for calculating Cumulative total, Percentage and ABC Bucket is perfect but the only thing i need to perform  before calculating the rest is sorting the Turn over in DESC order WRT to category - Country field 

Currenty i am facing this issue , the measure logic is sorting the turnover value among all the TO values irrespective of the Category - Country field. 

I would be highly grateful if you could refer to the Lastest data set and Share the solution.

[Jihwan_Kim]

Hi, @Rahul109 

Thank you for your feedback.

I am not sure if I understood your question correctly, but I think the cumulative total is based on the category. I think the number is showing correctly.

Or, please let me know how your desired outcome looks like.

Thanks.

[Rahul109]

Hello @Jihwan_Kim , I have checked again properly and Found out that cumulative total is coming correct.  Thank you for your help. 😃