Forum Discussion

mikemurray212's avatar
mikemurray212
Regular Visitor
8 years ago
Solved

Distributing values across categories

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.

 

CategoriesValue% of TOTAL (without Cat1 & Cat2)Cat1 & Cat2 ReallocationFinal Category Sum
Category 1100%0.00.0
Category 2200%0.00.0
Category 33017%5.035.0
Category 44022%6.746.7
Category 55028%8.358.3
Category 66033%10.070.0
TOTAL210100%30210
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    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

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    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