How to count rows depending on a measure

[brickanalyst]

Hello there,

I'd like to explain what i'm actually working on so it would be easier to find the solution I believe.

I have scenarios and there are two quotes red team and yellow team. Each scenario has variables and their values. Column structure:

scenario_id	variable_id	quantity_type	quantity	unit_cost	price	team_name
0001_abcd_99	XX123	lbs	10	0.5	5	teamA
0001_abcd_99	XX123	lbs	20	0.4	8	teamB

and I get the values by these functions:

team A price =

VAR _table =

    ADDCOLUMNS(

        'Appended_Table',

        "IsNumeric", IF( ISERROR( VALUE('Appended_Table'[Price]) ), 0, VALUE('Appended_Table'[Price]) ),

        "team", 'Appended_Table'[team_name]

    )

VAR _measure = SUMX( FILTER(_table, [team] = "A" ), [IsNumeric] )

RETURN _measure

same for team B and I can calculate variance between them and its average

variance between A & B= ABS( DIVIDE([team A price] - [team B price], [team B price], 0) )
avg variance between A & B = [variance between A & B] / DISTINCTCOUNT(Appended_Table[scenario_id])

What I'm stuck at finding the numbers of scenarios by a criteria. We want to find how many of scenarios are above 10%
average variance is already absolute value so, I created this dax below;

VAR measure_test =

    CALCULATE(

        DISTINCTCOUNT('Appended_Table'[Assumption_UID]),

        FILTER(

            ADDCOLUMNS(

            'Appended_Table',

            "assumption model uid variance", [avg variance between A & B]

        ),

        [avg variance between A & B] > 0.10

    ))

RETURN measure_result

and it returns 1 for all available scenarios, if there is no value on [avg variance between A&B] it doesn't return anything.

I could check on the table view if it's higher than 10% with this 

IF( [avg variance between A&B] > 0.1, 1, 0)

however, It shows zero on the card visual...
So, I'm missing something but I don't know what.

[brickanalyst]

Hi @tamerj1 

Can you explain a little bit how it works?

Thanks / Tesekkurler.

[tamerj1]

@brickanalyst 

SUMX iterates each scenario available in the current filter context (in the total cell for example, the scenarios available are the scenarios defined by the outer filter context), and evalute if the measure is > 0.1

INT converts TRUE to 1 and FALSE to 0

SUMX will act as a counter adding 1 for each true iteration thus counting the scenarios that fulfill the condition. 

[brickanalyst]

@tamerj1 Thank you !

[tamerj1 · Accepted solution]

Hi @brickanalyst 

please try

measure_test =
SUMX (
DISTINCT ( 'Appended_Table'[scenario_id] ),
INT ( [avg variance between A & B] > 0.10 )
)