Problem in Calculated Column

[myti]

Dear Friends,

I have created a summerzied table from another table,and now I faced a problem that how to implement a calculated columns.

As the below figure shows, First I would to add an index column which assign and index to the rows  based on the "Avg-position" value where their "cc","Type","Cluster,"Status" are the same. Then I would to make average on the  the first 20% minimum values of "Avg-Position" of each category. Hopefully my question is understandable.

Thank you,

Regards

Medi

[v-haibl-msft · Accepted solution]

@myti

You can try to create a calculated column with following DAX formula.

Rank = 
RANKX (
    FILTER (
        Table1,
        EARLIER ( Table1[CC] ) = Table1[CC]
            && EARLIER ( Table1[Type] ) = Table1[Type]
            && EARLIER ( Table1[Cluster] ) = Table1[Cluster]
            && EARLIER ( Table1[Status] ) = Table1[Status]
    ),
    Table1[Avg Position],
    ,
    ASC
)

About how to make average on the first 20% minimum values of "Avg-Position" of each category, could you please give a simple sample? You can tell us the calculate method and desired result what you want to get.

Best Regards,

Herbert

[myti]

@v-haibl-msft...Thank you very much for your soloution,it is perfect. 

Regarding the second part of my question,I bring an example based on the picture you posted after adding "Rank" column.

For example, the records have been assigned a rank from 1 to 12 in

case "cc=my","Type=category","Cluster=watches ","Status=Average".So it means that there are 12 records with the same CC,Type,cluster and status. Now,first, we calculate 20%*12=2.4.Since it is less than 2.5 we consider 2.Then now we calculate average of the  "Avg Positin"  related to Rank 1 and 2 (i.e. results of 20% of records).in our example means avg (0+10.76).So in the new calculated column,I would to have this calculated Average for all the records  that they have  (in our example)"cc=my","Type=category","Cluster=watches ","Status=Average".,which are 12 records.

Hopefully my example is clear.

Thanks again for your Cooperation

Medi

[v-haibl-msft]

@myti

Please try to creata another calculated column with following DAX expression to see if it works.

Average first 20% minimum = 
VAR RoundNum =
    ROUND (
        CALCULATE (
            MAX ( Table1[Rank] ),
            ALLEXCEPT ( Table1, Table1[CC], Table1[Type], Table1[Cluster], Table1[Status] )
        )
            * 0.2,
        0
    )
VAR Num =
    IF ( RoundNum = 0, 1, RoundNum )
RETURN
    (
        DIVIDE (
            CALCULATE (
                SUM ( Table1[Avg Position] ),
                FILTER (
                    ALLEXCEPT ( Table1, Table1[CC], Table1[Type], Table1[Cluster], Table1[Status] ),
                    Table1[Rank] <= Num
                )
            ),
            Num
        )
    )

Best Regards,

Herbert