DAX Measure to calculate Sum and Count from the created Measure

Question

I have data like this,

App_Num Days    Price
A1      10      100
A1      11      150
A2      11      200
A3      12      250
A3      12      300
A4      20      350
A4      21      400

Now, there is a parameter that the user uses to adjust the values and see. Total Value (Min and Max Range) For example, if the user selects 0-280- it is expected to list A1 (100 + 150 = 250 less than 280) and A2 (200 being less than 280).

I used a DAX like this and built a table like this,

Apps_in_scope = 

Var min_amount = Min('Total Value'[Total Value])

Var max_amount = Max('Total Value'[Total Value])

var required_app_num = SELECTEDVALUE(Table1[App_Num])

Var required_amount = CALCULATE(sum(Table1[Price]),FILTER(Table1,Table1[App_Num] = required_app_num)) 

var in_scope = if(And(required_amount <= max_amount, required_amount >= min_amount),1,0)

return in_scope

And I was able to produce a Visual like this,

App_Num Apps_in_scope
A1         1
A2         1
A3         0 
A4         0

Now, I want to create 2 Measures

It will list out the total number of transactions that are in scope (which is 2 for A1 and 1 for A2 outputting to 3 on the card).
It will give me the sum of A1 and A2 on card (100+150+200 = 450 on card).

How do I approach this. Kindly help me with this.

Alexis Olson Alexis Olson · Accepted Answer · 2021-03-02T22:51:20

You can approach this almost exactly like your previous question.

Using the more optimized approach:

CountInScope =
VAR apps =
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            Table1[App_Num],
            "@Rows", COUNT ( Table1[Price] )
        ),
        "@InScope", [Apps_in_scope]
    )
RETURN
    SUMX ( FILTER ( apps, [@InScpoe] = 1 ), [@Rows] )

and

SumInScope =
VAR apps =
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            Table1[App_Num],
            "@Price", SUM ( Table1[Price] )
        ),
        "@InScope", [Apps_in_scope]
    )
RETURN
    SUMX ( FILTER ( apps, [@InScpoe] = 1 ), [@Price] )

DAX Measure to calculate Sum and Count from the created Measure

1 Answers