convert excel formula to dax (averageifs)

[natabird3]

Hello,

I am trying to convert the following sequence of calculation from an excel file to power bi, but am unable to do so with the last one avergeifs formula as it is not existing in power bi for some reason. So i have a column that i need to rank (values will vary per ID). I do that using new calculated column: 

Rank Value = RANK.EQ(Data[Value],Data[Value],DESC)

Afterwards I need to round the count of the numbers as it varies based on the ID (i need to get top 2% values, top 5%, top 10%, top 20%, top 25% and top 50% only for each ID). I believe i can do it like this for top 2%: 

top 2% = ROUND(COUNT(Data[Rank Value])*0.02,0)

And finally to get the desired result:I need to do the averageifs but not sure how in power bi. Hope someone can help. Thanks in advance. 

[v-lid-msft · Accepted solution]

Hi @natabird3 ,

First I create a calculate column to rank value, you can another rank function as well,

Rank = RANKX('Table2','Table2'[Value],,DESC)

Then in another table, create a number column

Number = ROUND(COUNTROWS(Table2) * [TOP N],0)

Then we can create a measure to calculate the result.

Result =
FORMAT (
    AVERAGEX (
        FILTER ( ALL ( Table2 ), [Rank] <= SELECTEDVALUE ( 'Calculate'[Number] ) ),
        [Value]
    ),
    "Percent"
)

Or we can just use a calculate column to get same result

ResultColumn =
FORMAT (
    AVERAGEX ( FILTER ( Table2, [Rank] <= [Number] ), [Value] ),
    "Percent"
)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[v-lid-msft · Accepted solution]

Hi @natabird3 ,

First I create a calculate column to rank value, you can another rank function as well,

Rank = RANKX('Table2','Table2'[Value],,DESC)

Then in another table, create a number column

Number = ROUND(COUNTROWS(Table2) * [TOP N],0)

Then we can create a measure to calculate the result.

Result =
FORMAT (
    AVERAGEX (
        FILTER ( ALL ( Table2 ), [Rank] <= SELECTEDVALUE ( 'Calculate'[Number] ) ),
        [Value]
    ),
    "Percent"
)

Or we can just use a calculate column to get same result

ResultColumn =
FORMAT (
    AVERAGEX ( FILTER ( Table2, [Rank] <= [Number] ), [Value] ),
    "Percent"
)

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[natabird3]

Hello @v-lid-msft ,

Thank you for the proposed solution, i thought it works when i tried to implement it but now when i filter i am unable to get the desired result. I believe because of how we set the number formula, it counts the rows assuming each row is a different ID maybe, however we have some id's with multiple entries so if i filter per id i am not getting the desired result as nothing changes. For example, we can have the following situation, in this case i am getting the total for both ID's, anyway to only get per ID? Please let me know, thanks again for your help.

[v-lid-msft · Accepted solution]

Hi @natabird3 ,

The measure is dynamic but the calculated columns are computed during the database processing and then stored in the model. So In your scenario, we can only create a measure using following DAX to meet your requirement:

Result = 
VAR n =
    ROUND ( COUNTROWS ( 'Table' ) * SUM ( 'Calculate'[TOP N] ), 0 )
VAR t =
   ADDCOLUMNS ( 'Table', "rank", RANKX ( 'Table', [Value],, DESC ) )
RETURN
    FORMAT ( AVERAGEX ( FILTER ( t, [rank] <= n ), [Value] ), "Percent" )

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi

If this post helps, then please consider Accept it as the solution to help the other members find it more

[natabird3]

It works perfect, thanks for the help.