Getting percent on row while counting some columns

[Anonymous]

I have a situation where I have a table with individual feedback scores and want to produce a matrix showing rating groups and their percentage of the  year's total feedback but I don't want to add counts for a 0 score (meaning the question wasn't answered). I have looked at quite a few examples that seem similar but not quite the same. From looking at examples in this community, I have tried a few things I've seen and I have it close to correct but I think I am not doing it right. 

Date	Customer	Feedback score
12/02/2019	ABC	5
12/05/2019	SDF	7
04/20/2020	FDR	0
05/21/2020	GTR	6
06/12/2020	EWR	2
07/23/2021	QWE	7
08/21/2021	FDS	1
06/25/2022	ABC	0
07/12/2022	HJK	5

this would then consolidate to (counts shown):

Year	0	1-5	6-7	Total
2019	0	1	1	2
2020	1	1	1	3
2021	0	1	1	2
2022	1	1	0	2

What I would like to add is a percentage for the rating groups of 1-5 and 6-7 of their amounts of the total that doesn't include counts in the 0 column. For example, the first row both 1-5 and 6-7 would be 50%. 2020 it would also be 50% because the total count would be 2 (don't include the 0 column). 2022 would be 100% for the rating group of 1-5. 

I created two measures: 

# of non-zero feedbacks = 

CALCULATE(

COUNTROWS( 'Customer Feedback' ),

ALLEXCEPT( 'Customer Feedback', 'Customer Feedback'[score] ), 'Customer Feedback'[score] > 0

)

% of non-zero = count('Customer Feedback'[score])/ 'Customer Feedback'[# of Non-zero feedbacks]

Then I created a matrix that shows (this doesn't match the data above):

What it seems to do is correctly show on the very bottom total line (the 83% for 6-7 rating should be 83.3%, so it is probably just format but correct number) but for 2022, it should be 86.7% and not 5% (26 / 30). The 0 column doesn't really need a percentage because it isn't included in response rating percentages - so it really should be 0% and then the 1-5 and 6-7 percentages should be there. 

I had laid it out this way because it seemed easy to view the counts and percentages, but it can be changed to show the percentage next to the appropriate columns if making the calculations work is easier. 

Can someone help me figure the right measures and how to make the matrix to present this?

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

I got this!

Percentage Measure = 
VAR Numerator =
    CALCULATE(
        [FeedbackCount],
        'Customer Feedback'[feedback score] <> 0
    )
VAR Denominator =
    CALCULATE(
        [FeedbackCount],
        CALCULATETABLE(
            'Customer Feedback',
            'Customer Feedback'[feedback score] <> 0,
            REMOVEFILTERS('Customer Feedback'[feedback score (groups)])
        )
    )
RETURN

DIVIDE(
    Numerator,
    Denominator,
    BLANK()
)

[Anonymous]

ok so i stripped out all colummns except a couple but kept the full records so I can figure out what they should be easier. 🙂  I uploaded it to google drive so hopefully this works... https://docs.google.com/spreadsheets/d/1kVmuUrrEvUIbjOnYPh9YOeP4WoE42goL/edit?usp=sharing&ouid=10924... 

I was going to copy up also my trial PBI file but it kept showing a path with some restricted info in it for my data source so I don't think I can upload it - couldn't get it to point to the Google drive file. 

For the 6-7 column the percentage by year should be:

2022 = 86.7%

2021 = 84.8%

2020 = 79.8%

2019 = 86.2%

total of all years = 83.3%

The 2-5 (guess I have no 1's lol) would be the difference between 100 and the 6-7 percentages....

I used groups on the feedback score to provide the groupings...

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

I got this!

Percentage Measure = 
VAR Numerator =
    CALCULATE(
        [FeedbackCount],
        'Customer Feedback'[feedback score] <> 0
    )
VAR Denominator =
    CALCULATE(
        [FeedbackCount],
        CALCULATETABLE(
            'Customer Feedback',
            'Customer Feedback'[feedback score] <> 0,
            REMOVEFILTERS('Customer Feedback'[feedback score (groups)])
        )
    )
RETURN

DIVIDE(
    Numerator,
    Denominator,
    BLANK()
)

[Anonymous]

thanks!  I would've never gotten that....and I think I mostly understand what you did. lol

[littlemojopuppy]

@Anonymous glad I could help!  😊

[Anonymous]

I have a somewhat related question...if possible I'd like to use the value in the total row at the bottom of the 6-7 column (83.3%) in a card up top on the page...is there any easy way to get it? I'm guessing I need another measure(my guess), but would it be similar to what you helped with?

[littlemojopuppy · Accepted solution]

@Anonymous try another measure...should be something like this (maybe???)

CALCULATE(
	[The Measure I Gave You Earlier],
	[Group/bin] = "6-7"
)

[Anonymous]

ok! I tried that a little, but it's the second parameter that I didn't add....thanks! those extra parameters get me every time.  Tried it as I typed and that did it. Thanks!!

[Anonymous]

me too! I knew there had to be a way, but I'm just not good enough to know enough functions to do it. I have two others that I need something similar so I should be able to take what you provided to do it for those....hopefully. 🙂  Thank you again!

[littlemojopuppy]

Hi @Anonymous 

Try this...

Average Measure =
AVERAGEX(
	FILTER(
		'Customer Feedback',
		'Customer Feedback'[score] > 0
	),
	'Customer Feedback'[score]
)

Hope this helps!

[Anonymous]

do I replace the percentage one I have with this one or do I add it somewhere in addition?

[littlemojopuppy]

@Anonymous replace

[Anonymous]

I tried replacing and now it is giving me the average score I think and not the percentage...see the change below in numbers....

[littlemojopuppy]

@Anonymous I'm an idiot.  I have no idea why I gave you a formula for average...

This should be better

Percentage =
DIVIDE(
	COUNT('Customer Feedback'[score]),
	COUNTROWS(
		FILTER(
			'Customer Feedback',
			'Customer Feedback'[score] > 0
		)
	),
	BLANK()
)
	

[Anonymous]

thanks. I was trying so many things yesterday and tried the Average a couple of times... 🙂 

I replaced it with your DIVIDE and it's still not right:

I also tried playing with the 'Show value as" options but they didn't help either. 

[littlemojopuppy]

@Anonymous can you provide some sample data to play with?

[Anonymous]

I'll try to make some...what I have is a lot and so I will try to shrink it and figure what it should produce too...might take me a bit...thanks.

[littlemojopuppy]

@Anonymous a couple hundred records should be enough.  Copy and paste data into Excel, change any sensitive data (company or person names, etc.) and share a link to Google Drive, One Drive, etc.