Measure with filter and 2 conditions on one table

[tcouey]

Hi and thanks for any help, it greatly appreciated.

I am trying to divide the following using a measure:

Count of control ids marked as BOTH of the following conditions from the controls table. controls[Control_classification] = "Mgmt" where controls[mrc_control] also = "Y"

That count of control ids then divided by the total count of control id's where controls[mrc_control] = "Y"

This is where I'm at but not sure how to add in controls[mrc_control] = "Y" to the first portion of my division.

% Of MRC Controls = DIVIDE(COUNTX(FILTER(Controls,controls[control_classification] = "Mgmt"),controls[id]), COUNTX(FILTER(Controls,controls[mrc_control] = "Y"),controls[id]))

Right now my measure is just dividing control ids marked as mgmt by control ids marked as mrc_control

[v-yanjiang-msft · Accepted solution]

Hi @tcouey ,

You are missing a condition in your formula, modify it like this:

% Of MRC Controls =
DIVIDE (
    COUNTX (
        FILTER (
            Controls,
            controls[control_classification] = "Mgmt"
                && controls[mrc_control] = "Y"
        ),
        controls[id]
    ),
    COUNTX ( FILTER ( Controls, controls[mrc_control] = "Y" ), controls[id] )
)

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[speedramps]

Assume you want answer in card .

Answer =

// create 2 subsets

VAR subsetMGMTY = 
FILTER(tablename,
control_classification = "MGMTY" &&
mrc_control= "Y"
)

VAR subsetY = 
FILTER(tablename,
mrc_control= "Y"
)

RETURN
// get your answer

DIVIDE(
COUNTROWS(subsetMGMTY),
COUNTROWS(subsetY)
)

Drop the measure into a card visual and format the appearance.

Please click thumbs up and accept as solution buttons. Thanks 😀

[speedramps]

Please can you provide some example data in a table format (not a screen shot)

and an example of the desired output with a better description so we can help you. Thank you
Hide any private date. 

[tcouey]

In the "controls" table below, we have columns for id mrc_control, and control_classification. I need to divide the total count of ids where control_classification = MGMT and mrc_control= Y, by the total count of ids where MRC_Control = Y. 

In the example table below, I would have one divided by 3, since I have one ID that is Y for mrc_control, and also MGMT for control_classification, and I have 3 total ids marked Y for mrc_control. My end result would be 33% which is what I am looking for. 

id	mrc_control	control_classification
1	Y	MGMT
2	Y	Exec
3	Y	Exec
4	N	Business
5	N	MGMT
6	N	Business