Forum Discussion

rommel20's avatar
rommel20
Resolver I
8 years ago
Solved

Percentage within Percentage

Hi,

 

is there any way i can create Percentage withn Percentage for e.g

 

Group       Percentage

Teacher      5%

Engineer     90%

Programmer 5%

 

if i selected teacher i will show the percentage based on Group Percentage

 

Subgroup                  Percentage

Math Teacher             3%

English Teacher           1.5%

Science Teacher           1.5%

 

I used this first formula in Group

% Value = DIVIDE(SUM(Value),CALCULATE(SUM(Value]),ALL(Group)))

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    rommel20

     

    According to your description, you want to calculate the percentage of parent when drilling down to child level. Right? 

     

    In this scenario, you have to specify different column as filter in CALCULATE(). It can be like: 

     

    % Value =
IF (
    HASONEVALUE ( Table[SubGroup] ),
    DIVIDE (
        SUM ( Tble[Value] ),
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[SubGroup] ) )
    ),
    DIVIDE (
        SUM ( Tble[Value] ),
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[Group] ) )
    )
)

    Please refer to article below: 

    PowerPivot: Calculate ratio to parent

     

    Regards,

     

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    8 years ago

    rommel20

     

    According to your description, you want to calculate the percentage of parent when drilling down to child level. Right? 

     

    In this scenario, you have to specify different column as filter in CALCULATE(). It can be like: 

     

    % Value =
IF (
    HASONEVALUE ( Table[SubGroup] ),
    DIVIDE (
        SUM ( Tble[Value] ),
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[SubGroup] ) )
    ),
    DIVIDE (
        SUM ( Tble[Value] ),
        CALCULATE ( SUM ( Table[Value] ), ALL ( Table[Group] ) )
    )
)

    Please refer to article below: 

    PowerPivot: Calculate ratio to parent

     

    Regards,