Pecentage in a matrix with grouping Help

Question

using this:percent = DIVIDE(SUM('Table1 (2)'[People]), CALCULATE(COUNT('Table1 (2)'[People]),ALLEXCEPT('Table1 (2)','Table1 (2)'[People])))&nbsp;I was able to get a percentage but the calculation needs to work within the first group.&nbsp; Otherwise the percentage is wrong as it works on the total of people and not the sub totals.&nbsp;How do get the calculations to work within groups?&nbsp;ie&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y3Group&nbsp; JOB &nbsp; people&nbsp;&nbsp;&nbsp; %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; people&nbsp;&nbsp; %&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; people&nbsp;&nbsp;&nbsp; %first&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 &nbsp; &nbsp; &nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Total&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100Second&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Total&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;

vvelarde · Answer

Mart&nbsp;hi, you need to modified the AllExcept, Just mantain the filter Group and it works&nbsp;% =
    DIVIDE (
        CALCULATE ( COUNT ( Table1[People] ) ),
        CALCULATE ( COUNT ( Table1[People] ), ALLEXCEPT ( Table1,Table1[Group] ) )
    )

v-caliao-msft · Answer

Hi Mart,
&nbsp;
You could use the DAX below.
Measure = sum(Table2[Amount])/CALCULATE(SUM(Table2[Amount]),ALLEXCEPT(Table2,Table2[Group]))
Sample data
&nbsp;
Regards,
Charlie Liao

mart · Answer

Thanks Victor&nbsp;Almost there but percentage still wrong after first total row, you can't have more than 100%&nbsp;Will try to upload new image

mart · Answer

Charlie&nbsp;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?&nbsp;Mart

v-caliao-msft · Answer

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.
&nbsp;
Regards,
Charlie Liao

Forum Discussion

Pecentage in a matrix with grouping Help

6 Replies

Related content

Matrix column not grouped

Fabric & Azure Data User Group Program Requirements & Benefits

Matrix Adding total from previous Group

Community User Group Experience

matrix visual with calculation group