Forum Discussion

brickanalyst's avatar
brickanalyst
Icon for Resolver I rankResolver I
1 year ago
Solved

How to count rows depending on a measure

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_idvariable_idquantity_typequantityunit_costpriceteam_name
0001_abcd_99XX123lbs100.55teamA
0001_abcd_99XX123lbs200.48teamB

 

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.




"DAX " "DAX help" 'Need help'
DAX
measure
  • tamerj1's avatar
    tamerj1
    1 year ago

    Hi brickanalyst 

    please try

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

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi brickanalyst 

    please try

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

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      1 year ago

      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.