Pecentage in a matrix with grouping Help

[Mart]

using this:

percent = DIVIDE(SUM('Table1 (2)'[People]), CALCULATE(COUNT('Table1 (2)'[People]),ALLEXCEPT('Table1 (2)','Table1 (2)'[People])))

I was able to get a percentage but the calculation needs to work within the first group.  Otherwise the percentage is wrong as it works on the total of people and not the sub totals.

How do get the calculations to work within groups?

ie                           Y1                  Y2                     Y3

Group  JOB   people    %        people   %        people    %

first       1        50        50

             2        50        50

          Total    100       100

Second  3        75        75

             4        25        25

          Total    100       100

Total              200

[v-caliao-msft]

Hi @Mart,

You could use the DAX below.

Measure = sum(Table2[Amount])/CALCULATE(SUM(Table2[Amount]),ALLEXCEPT(Table2,Table2[Group]))

Sample data

Regards,

Charlie Liao

[Mart]

Charlie

Thats ecactly what I want, but I cant repeate it I will start again from scratch.

Could it be because I have it in a matrix?

Mart

[v-caliao-msft]

@Mart, Could you please provide us more information why you cannot do it? If possible providue us some screenshot, so that we can make further analysis.

Regards,

Charlie Liao

[Mart]

Cracked it! At least I have the correct numbers

I used 3 new measures

First New Measure

GrandTotal = CALCULATE(SUM(Table2[People]),GROUPBY(Table2,Table2[Group]),ALLEXCEPT(Table2,Table2[Year],Table2[People]))

Second New Measure

PeopleSum = SUM(Table2[People])

Third New Measure

Percent = Table2[PeopleSum]/Table2[GrandTotal]

Question

Is there an easier way?

[Vvelarde]

@Mart

hi, you need to modified the AllExcept, Just mantain the filter Group and it works

% =
    DIVIDE (
        CALCULATE ( COUNT ( Table1[People] ) ),
        CALCULATE ( COUNT ( Table1[People] ), ALLEXCEPT ( Table1,Table1[Group] ) )
    )

[Mart]

Thanks Victor

Almost there but percentage still wrong after first total row, you can't have more than 100%

Will try to upload new image