Help: percentage of row totals with categorical data

[afaherty]

Hello,

I have spent many hours attempting to solve this issue but have been unsuccessful.

Here is some dummy data similar to my actual data:

StudentID	Competition	Rating
1	Decimals	Above Average
1	Fractions	Average
1	Equations	Below Average
1	Essay Writing	Average
1	Thesis	Average
1	Outline Prep	Below Average
2	Decimals	Above Average
2	Fractions	Above Average
2	Equations	Average
2	Essay Writing	Below Average
2	Thesis	Below Average
2	Outline Prep	Average
3	Decimals	Average
3	Fractions	Below Average
3	Equations	Average
3	Essay Writing	Above Average
3	Thesis	Average
3	Outline Prep	Above Average
4	Decimals	Below Average
4	Fractions	Below Average
4	Equations	Below Average
4	Essay Writing	Average
4	Thesis	Average
4	Outline Prep	Average
5	Decimals	Above Average
5	Fractions	Above Average
5	Equations	Above Average
5	Essay Writing	Average
5	Thesis	Below Average
5	Outline Prep	Average

If I get the total of distinct students with N = DISTINCTCOUNT(StudentID) and then I place that measure into the Values section twice, and then tell PowerBI to take the 2nd instance of the measure and show me the % of row total, then it looks great. However, the problem is that I want a DAX measure for this - I do not want to rely on choosing the option for it to show me the % of row total. Every DAX formula I have tried produces 100% all the way down each % column. I am waving my white flag! Thank you to anyone who can help!

[MasonMA · Accepted solution]

@afaherty 

Hi, you may update the measure to below to isolate the row-level total.

DIVIDE( DISTINCTCOUNT(StudentRatings[StudentID]), 
CALCULATE( DISTINCTCOUNT(StudentRatings[StudentID]), 
REMOVEFILTERS(StudentRatings[Rating]), ALLSELECTED() ) )   

[FBergamaschi]

The code should be

DIVIDE ( 
           N,
           CALCULATE ( N, REMOVEFILTERS ( Rating ) )
)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[afaherty]

Hi @FBergamaschi ,

Thank you so very much. Your solution worked perfectly in my dummy PowerBI file. However, in my real file (which I can't post here), it's still producing 100%'s all the way down each rating column. I do have slicers for multiple variables, but I think that shouldn't matter. I am so confused!

[MasonMA · Accepted solution]

@afaherty 

Hi, you may update the measure to below to isolate the row-level total.

DIVIDE( DISTINCTCOUNT(StudentRatings[StudentID]), 
CALCULATE( DISTINCTCOUNT(StudentRatings[StudentID]), 
REMOVEFILTERS(StudentRatings[Rating]), ALLSELECTED() ) )   

[afaherty]

This worked in my real dataset! Thank you so much! 

[FBergamaschi]

Ciao @afaherty 

just to be sure I get the thing

my code

DIVIDE ( 
           N,
           CALCULATE ( N, REMOVEFILTERS ( Rating ) )
)

did not work on your real dataset

@MasonMA  code

VAR CurrentCount =
    DISTINCTCOUNT(StudentRatings[StudentID])

VAR TotalCount =
    CALCULATE(
        DISTINCTCOUNT(StudentRatings[StudentID]),
        REMOVEFILTERS(StudentRatings[Rating])
    )
RETURN
DIVIDE(CurrentCount, TotalCount, 0)

did work?

That would be weird as the codes are identical 🙂

Thanks for a clarification

Best

F

[afaherty · Accepted solution]

@FBergamaschi I am rather confused now because this morning when I used the code from MasonMA that I now see above it did not work and I got 100% all the way down the column:

VAR CurrentCount =
    DISTINCTCOUNT(StudentRatings[StudentID])

VAR TotalCount =
    CALCULATE(
        DISTINCTCOUNT(StudentRatings[StudentID]),
        REMOVEFILTERS(StudentRatings[Rating])
    )
RETURN
DIVIDE(CurrentCount, TotalCount, 0)

However, when I look at the email notification I received when MasonMA replied to my post, here is what his response said:

That code mentioned in the picture above is what worked, but I no longer see this post from MasonMA in this thread at all. 

So, in conclusion, here is what did work:

DIVIDE( DISTINCTCOUNT(StudentRatings[StudentID]), 
CALCULATE( DISTINCTCOUNT(StudentRatings[StudentID]), 
REMOVEFILTERS(StudentRatings[Rating]), ALLSELECTED() ) )   

[MasonMA]

As you mentioned specifically in messages that you have Slicers on report. ALLSELECTED () should be a more reliable solution because it preserves slicers, filters, and row context, and you only remove the Rating within the selected scope. I've updated the solution. Apology.