Cumulative categories

[#1]

Hi everyone

You will see attached that I have ItemCodes with a Volume Figure and a Relative %.

I want to place my ItemCodes into 3 categories based on their volumes.

I want the ItemCodes that contribute 70% to TOTAL VOLUME to be category A (0% - 70%).

I want the ItemCodes that contribute 20% to TOTAL VOLUME to be category B (70.1% - 90%).

I want the ItemCodes that contribute 10% to TOTAL VOLUME to be category B (90.1% - 100%).

Do anyone maybe have a way to do this.

Thank you

[deepakk]

hi,

PFA, You data doesnt satify the last two conditions. I hope the attach application is what you are looking for.

[stephencredmond]

Is this something you want in the load script or to be dynamic?

You could try a calculated dimension:

=if(aggr(RangeSum(Above(Sum([TOTAL VOLUME]), 0, RowNo())) / Sum(total [TOTAL VOLUME]),[ITEM CODE]) <= .7, 'Category1', if(aggr(RangeSum(Above(Sum([TOTAL VOLUME]), 0, RowNo())) / Sum(total [TOTAL VOLUME]),[ITEM CODE]) <= .9, 'Category2', 'Category3'))

Regards,

Stephen

[#4]

Hi Stephen

Thanks for the feedback. I would like this to be dynamic as I would like to see which products is category A,B or C depending on the date range I select.

I copied your dimension into my model. The result is that all my categories are category 3.

This is the expression I use to get TOTAL VOLUME:

sum

({$<InvntItem = {$(='Y')}>}(INVQuantity*PackSize))

-

sum

({$<InvntItem = {$(='Y')}>}(CNQuantity*PackSize))

+

sum

({$<InvntItem = {$(='Y')}>}(DRUNINVQuantity*PackSize))

-

sum

({$<InvntItem = {$(='Y')}>}(DRUNCNQuantity*PackSize))

This is the ITEM CODE field name: ItemCode

Would you please help me how to incorporate this information into your solution given before?