Measure to calculate a status rate by site name

[SophRow]

Hi,

I have two columns of data - the first is a status column, the second is site name. 

I want to calculate the rate of each status by total number of rows for each site. There are only 4 status's but there are 27 sites! Is there a quick way with DAX to calculate #status divided by total rows for each site, without having to manually create measures for each site?

Best,

Soph

[SophRow]

Thanks so much for helping Polly!

What I'm trying to create is similar to the below to tease out each status per total visits rather than per randomised participant:

Do you know how I could do that with your measure?

Best wishes,

Soph

[Anonymous]

Hi @SophRow ,

How to  tease out each status per total visits ? Could you please provide some sample data and your desired output?

 please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[SophRow]

Hi Polly - I figured it out! Thanks again for your help - that measure is awesome

[Anonymous · Accepted solution]

Hi @SophRow ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _site =
    CALCULATE (
        COUNT ( 'Table'[site name] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[site name] = SELECTEDVALUE ( 'Table'[site name] )
        )
    )
VAR _status =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = SELECTEDVALUE ( 'Table'[status] )
                && 'Table'[site name] = SELECTEDVALUE ( 'Table'[site name] )
        )
    )
RETURN
    _status / _site

Or a column.

Column =
VAR _site =
    CALCULATE (
        COUNT ( 'Table'[site name] ),
        FILTER ( ALL ( 'Table' ), 'Table'[site name] = EARLIER ( 'Table'[site name] ) )
    )
VAR _status =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = EARLIER ( 'Table'[status] )
                && 'Table'[site name] = EARLIER ( 'Table'[site name] )
        )
    )
RETURN
    _status / _site

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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