Distributing values across categories

[mikemurray212]

Hi all - hope you can help.  I have a need to redistrubute values from some categories across the remaining categories based on the overall percentage the remaining categories have.  

In the example below, I want to take the total value of Category 1 and Category 2 and spread it across Categories 3-6 based on their percentage of the sum total of categories 3-6.  the table below shows the walk from initial value to what I am looking to achieve in "Final Category Sum".  Any help is appreciated.

Categories	Value	% of TOTAL (without Cat1 & Cat2)	Cat1 & Cat2 Reallocation	Final Category Sum
Category 1	10	0%	0.0	0.0
Category 2	20	0%	0.0	0.0
Category 3	30	17%	5.0	35.0
Category 4	40	22%	6.7	46.7
Category 5	50	28%	8.3	58.3
Category 6	60	33%	10.0	70.0
TOTAL	210	100%	30	210

[v-yuta-msft · Accepted solution]

Hi mikemurray212,

To achieve your requirement, create three calculate columns using DAX below:

% of TOTAL (without Cat1 & Cat2) = 
IF (
    Table1[Categories] = "Category 1"
        || Table1[Categories] = "Category 2",
    0,
    Table1[Value]
        / CALCULATE (
            SUM ( Table1[Value] ),
            FILTER (
                Table1,
                Table1[Categories] <> "Category 1"
                    && Table1[Categories] <> "Category 2"
            )
        )
)

Cat1 & Cat2 Reallocation = 
IF (
    Table1[Categories] = "Category 1"
        || Table1[Categories] = "Category 2",
    0,
    Table1[% of TOTAL (without Cat1 & Cat2)]
        * CALCULATE (
            SUM ( Table1[Value] ),
            FILTER (
                Table1,
                Table1[Categories] = "Category 1"
                    || Table1[Categories] = "Category 2"
            )
        )
)

Final Category Sum = 
IF (
    Table1[Categories] = "Category 1"
        || Table1[Categories] = "Category 2",
    0,
    Table1[Value] + Table1[Cat1 & Cat2 Reallocation]
)

 

Regards,

Jimmy Tao