topic Re: How to count rows depending on a measure in DAX Commands and Tips

How to count rows depending on a measure

brickanalyst — Thu, 21 Nov 2024 15:11:23 GMT

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.

Re: How to count rows depending on a measure

tamerj1 — Thu, 21 Nov 2024 18:35:19 GMT

Hi brickanalyst

please try

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

Re: How to count rows depending on a measure

brickanalyst — Fri, 22 Nov 2024 14:58:12 GMT

Hi tamerj1

Can you explain a little bit how it works?

Thanks / Tesekkurler.

Re: How to count rows depending on a measure

tamerj1 — Fri, 22 Nov 2024 19:36:48 GMT

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.

Re: How to count rows depending on a measure

brickanalyst — Tue, 26 Nov 2024 16:44:40 GMT

tamerj1 Thank you !