Calculation (dividing a number that includes a filter by the total number)

[Anonymous]

Hi,

I've recently just started using PowerBI as our company has adopted it. I don't have much coding experience but oustide of the coding aspect I've became very familiar with the tool as I've been using it. I'm trying to write a calculation or measure that counts the number of total responses if they are a certain number and divide that number over the total number of responses. An example would be that we have customer satisfaction surveys and let's just say it's a 1-5 scale with 1 being the lowest and 5 being the highest. Let's say 10 total submissions came in and 5 of those were rated under 3 and the other 5 were rated 4 and above. We only consider 4s and 5s as satisified. That would give us a total of 5 submissions that were 4 or 5. and considered "satisfied". The calculation I'm looking for would say count the number of satisified submissions which is a total of 5 (we're only counting 4s and 5s as satisfied) and divide that by the total number of submissions which is 10. How would that formula look? I've tried looking around online but I haven't found a scenario that's close to mine to reference. Thanks in advance for any help. 

[v-lili6-msft · Accepted solution]

hi, @Anonymous

Assume this is the basic data

Then we can create a measure by DAX Function DIVIDE like this

Measure = 
DIVIDE (
    CALCULATE (
        COUNTA ( 'Table'[Sub ID] ),
        FILTER ( 'Table', 'Table'[Score] >= 4 )
    ),
    CALCULATE ( COUNTA ( 'Table'[Sub ID] ) )
)

Then darg it into visual

CALCULATE (
COUNTA ( 'Table'[Sub ID] ),
FILTER ( 'Table', 'Table'[Score] >= 4 )

is calculate count Sub ID whicn satisfied >=4

you could also use this formula

CALCULATE (
COUNTA ( 'Table'[Sub ID] ),
FILTER ( 'Table', 'Table'[Score] = 4 || 'Table'[Score] = 5)

 and 

CALCULATE ( COUNTA ( 'Table'[Sub ID] ) )

is calculate Count(all the Sub ID)

By the way, if you don't have much coding experience and are not familiar with DAX Function, 

I suggest you to learn DAX and familiar with POWER BI first.

https://docs.microsoft.com/en-us/power-bi/desktop-what-is-desktop

Best Regards,
Lin