Calculating counts and % of entries from multiple columns

[Anonymous]

Hi All,

I have a dataset like this:

Date	User	Level 1	Level 2	Level 3
3-24-2021	bob	bad		n/a
3-25-2021	chip	good	bad	good
3-26-2021	terry	bad	worse	bad
3-27-2021	bob	bad	bad
3-28-2021	tim	bad	good

Which I need to talley each category of feedback from multiple columns, but I'm having some issues getting them to count properly, since they are sharing rows. What I'm going for is to have a visual show something like this:

Feedback	Count	Share %
n/a	1	8.3%
good	3	25.0%
bad	7	58.3%
worse	1	8.3%

I'm able to get tallys working, but only for one "Level" at a time, since it's counting entries from the other levels that have something on the same row whenever I try to count from all three columns at once. I tried making a pivot table, but it didn't work out well since I also need to be able to still slice by User and Date.

Where do I begin making measures for Count and % to deal with this dataset?

[selimovd · Accepted solution]

Hey @Anonymous ,

create a new table to get distinct values for the Feedback:

Feedback = 
DISTINCT(
    UNION(
        VALUES( myTable[Level 1] ),
        VALUES( myTable[Level 2] ),
        VALUES( myTable[Level 3] )
    )
)

Rename the column [Level 1] to [Levels] in the new table Feedback.

In case the table you posted first is called myTable add the following measure:

Count =
    CALCULATE(
        COUNTROWS( myTable ),
        TREATAS( SUMMARIZE( Feedback, Feedback[Levels] ), myTable[Level 1] )
    )
    +
    CALCULATE(
        COUNTROWS( myTable ),
        TREATAS( SUMMARIZE( Feedback, Feedback[Levels] ), myTable[Level 2] )
    )
    +
    CALCULATE(
        COUNTROWS( myTable ),
        TREATAS( SUMMARIZE( Feedback, Feedback[Levels] ), myTable[Level 3] )
    )

This should give you the result you want:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

How would I go about getting the % share of each item over the total of all feedback?

[Anonymous]

That worked perfectly!
Exactly the funtionality i was looking for-
The slicers also worked seemlessly.

Thanks a bunch!