calculating percentages from original column count for different slicer selections

[lukeSDM]

Hi,

I have been pulling my hair out for a few hours now trying to figure this out.

I have a slicer from a column containing qualifications and I select English Language from the slicer.

I have a card for the count (how many students took the English Language exam) of the qualification.

When English language is selected the percentages for which student achieved which grade(1-9) is correct, E.G. number of students achieving a certain grade / count of students who took the exam.  This is great.

However, when I filter with my other slicer for the grade achieved (1-9) for example for grades 6 and 7 the percentages change to the count of the students who achieved a 6 or a 7 instead of the overall total who took the exam!

In an ideal world I would like to keep the orginal percentages (number of students achieving a certain grade / count of students who took the exam.) and would also like to have soemthing that will add the percentages selected from the slicer.

I hope this makes sense in what I am trying to achieve and any help is extremely appreciated!!this is when no grade from the grade slicer is selected so the percentages are correct and the count card is correctthis shows when grade 6 and 7 is selected with the wrong percentages and also not having an addition for the correct percentages

[v-alq-msft]

Hello, @lukeSDM

Based on your description, I created data to reproduce your scenario.

Mesa:

You can create a measure as shown below.

Percentage = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Grade],
        "Num of Grade",
        var _garde = [Grade]
        return
        CALCULATE(
            DISTINCTCOUNT('Table'[Stu_id]),
            FILTER(
                ALLSELECTED('Table'),
                'Table'[Grade]=_garde
            )
        ),
        "Num of Total",
        CALCULATE(
            DISTINCTCOUNT('Table'[Stu_id]),
            ALL('Table')
        )
    ),
    [Num of Grade]/[Num of Total]
)

Next, you should display the result with 'Stacked Column Chart'.

Best regards

Allan

If this post helps,then please consider accepting it as the solution to help other members find it more quickly.

[lukeSDM]

Hi Allan/ @v-alq-msft 

Have you managed to have a look at my previous response?

I still am having difficulty with resolving my issue.

I really appreciate your time and help.

Thank you for assisting a newbie!

Kind Regards,

Luke

[lukeSDM]

@v-alq-msft 

I have tried this and it was partially successful!

Instead of calculating the percentage by every student in the spreadsheet i need to calculate it by every student who took a specific subject.

E.G. 25 students took Art and 5 of them got a grade 5, so it would need to be 5/25 *100 for the percentage.

This is the only change I believe , so if you know of a way to do this it would be extremely helpful!

Thank you for your support so far.This is what my data looks like, hopefully this might help you get a better picture of what i am trying to do!?

[lukeSDM]

@v-alq-msft 

Could I please ask you if there is an easy way to display the addition of the selected grade value percentages?

E.G. grade 3 = 30% and grade 4=25% , and when grade 3 and 4 is selected is selcted i would liek to show the combined percentage?

Thanking you in advance!

[v-alq-msft]

Hi, @lukeSDM 

If you want to show the combined percentage for all selected grade. You just need to remove the 'Grade' from the 'Legend'.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukeSDM]

Thank you for your reply! @v-alq-msft 

I think this may have resolved it!

I am going to do a bit more testing and will then get back to you and 'accept as solution' if all is good! 

[parry2k]

@lukeSDM you need to update your count of student measure

count of student measure = CALCULATE ( COUNTROWS ( StudentTable ), ALL ( StudentTable[Grade] ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[lukeSDM]

Hi, 

Unfortunately this did not resove my issue, as the percentages ares till incorrectly calculated as it is not dividing by the amount of students who took that exam but only for the ones who achieved that grade which i select a value in the slicer.

@parry2k