Newbie Problem with showing value as a percentage of row GRAND total

[degausser86]

Hi Gang,

New member here.

Classroom teacher who would like some assitance from you experts out there! I'm sure it's a relatively easy fix, when you know how!

I've come to a point where I dont think Excel can solve what i want in pivot tables, but basically I have joined several excel files with power query and want to display some school wide data from multiple departments.

Basically, I want to see what percentage of students experiencing financial hardship (Y in this table below) fit into each ethnicity. I want to see the number of students as a percentage of all students that experience hardship (i.e all the Y's in the table that are unique, otherwise i'll count the same student more than one time for each subject they are enrolled on) It's almost as if i want to work it out as a percentage of a row as a GRAND total if they are Y?

For example, if there were 10 total students with a Y and 5 of them happened to appear in the white british, then i want the percentage column to read 50%, rather than taking the value as a a percentage of the Y's in that category only as this does as 100%.

I can't think how I can do this?! Is it a measure? some sort of FILTER, but I can't work it out as I dont want to do multiple measures for other columns in this table?

Apologies that this post is probably not in the most accurate lingo, I'm teaching myself this!

Please help!

[degausser86]

@amitchandak Thanks for the quick response. Would I then have to do another one for the N in hardship. Would it display in the end column or would there be 2 columns?

EDIT: I've also had a chance to run the measure and it doesnt seem to work precisely. I cant put my finger on it.

Where there are 178 students selected in total, the 135 in one category should be calculating as 75.8%, but its coming out with the measure as 74?

Any thoughts?

[degausser86 · Accepted solution]

With the help of CHAT GPT, I worked out that the code should be this.

% MIX of grand Total = DIVIDE(

        DISTINCTCOUNT('1st year polar'[ULN]),

    CALCULATE(

        DISTINCTCOUNT('1st year polar'[ULN]),

        ALLSELECTED('1st year polar')

    )

)

[amitchandak]

@degausser86 , a measure like

divide(countrows(filter(Table, table[hardship] ="Y")), countrows(filter(allselected(Table), table[hardship] ="Y")))

or

divide(calculate([Meausre], filter(Table, table[hardship] ="Y")), calculate([Meausre],filter(allselected(Table), table[hardship] ="Y")))