DAX Measure to calculate average with Parameters inside it

2
votes

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

The average of the days is displayed on a card visual as 13.857.

Now, there are two parameters that are set for user to adjust the values and see.

  1. Total Value (Min and Max Range)
  2. Days

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 after selecting the total price range, if the user selects the days parameter manually to be 15 then my average will shift as per this logic.

  • A1 has 2 transactions and with in the selected price range of 280 will become (15*2)
  • A2 has 1 transaction and with in the selected price range of 280 become (15*1)
  • A3 has 2 transaction and will remain unchanged (12+12)
  • A4 has 2 transactions and will remain unchanged (20+21)

So my new measure which I want to place on the card is expected to show now (15+15+15+12+12+20+21)/7 = 15.714

How can I write this measure. Kindly help me with this

2
powerbidaxpowerbi-desktop

2 Answers

2
votes

I'd tweak your measure slightly so that it works better for taking the average:

Apps_in_scope_2 =
VAR min_amount = MIN ( 'Total Value'[Total Value] )
VAR max_amount = MAX ( 'Total Value'[Total Value] )
VAR required_amount =
    CALCULATE ( SUM ( Table1[Price] ), ALLEXCEPT ( Table1, Table1[App_Num] ) )
VAR in_scope =
    IF ( AND ( required_amount <= max_amount, required_amount >= min_amount ), 1, 0 )
RETURN
    in_scope

With this tweak the average is fairly simple:

AvgMeasure =
VAR DaysParam = SELECTEDVALUE ( DaysSlicer[Days] )
RETURN
    AVERAGEX( Table1, IF( [Apps_in_scope_2] = 1, DaysParam, Table1[Days] ) )

Edit:

Here's an alternative version that doesn't use the first measure but should scale better to large data tables.

AvgAlternate =
VAR min_amount = MIN ( 'Total Value'[Total Value] )
VAR max_amount = MAX ( 'Total Value'[Total Value] )
VAR DaysParam = SELECTEDVALUE ( DaysSlicer[Days] )
VAR apps =
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            Table1[App_Num],
            "@Price", SUM ( Table1[Price] ),
            "@Rows", COUNT ( Table1[Price] )
        ),
        "@Days",
            IF (
                AND ( [@Price] <= max_amount, [@Price] >= min_amount ),
                DaysParam * [@Rows],
                CALCULATE ( SUM ( Table1[Days] ) )
            )
    )
RETURN
    DIVIDE ( SUMX ( apps, [@Days] ), SUMX ( apps, [@Rows] ) )
2
votes

This is assuming that you have separate tables for your Price range and Days selection (as in what-if parameter tables).

My measure =
VAR apps =
    SELECTCOLUMNS (
        FILTER (
            SUMMARIZE ( Table1, Table1[App_Num], "Total Price", SUM ( Table1[Price] ) ),
            [Total Price] >= MIN ( 'Total Value'[Total Value] )
                && [Total Price] <= MAX ( 'Total Value'[Total Value] )
        ),
        "App_Num", [App_Num]
    )
RETURN
    AVERAGEX (
        Table1,
        IF ( Table1[App_Num] IN apps, SELECTEDVALUE ( Days[Days] ), Table1[Days] )
    )

enter image description here