Forum Discussion

natabird3's avatar
natabird3
Icon for Skilled Sharer rankSkilled Sharer
7 years ago
Solved

convert excel formula to dax (averageifs)

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. 
 
 
 
 
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    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's avatar
    v-lid-msft
    7 years ago

    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

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      natabird3
      Icon for Skilled Sharer rankSkilled Sharer
      7 years ago

      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's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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