Forum Discussion

sdrfghjkl's avatar
sdrfghjkl
Frequent Visitor
3 years ago

Dynamic Ranking measure and a card Visual

Hi,    I need some help with creating Dynamic Ranking and extracting a rank based on a particular column as a filter. My table has the following columns    Date  brand brand_type category ...
Need Help
v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi sdrfghjkl ,

 

Please try:

Rank =
VAR _a =
    ADDCOLUMNS (
        ALL ( Customer_Feedback ),
        "Rank",
            RANKX (
                ALL ( Customer_Feedback[brand] ),
                CALCULATE (
                    AVERAGE ( Customer_Feedback[rating] ),
                    FILTER (
                        ALL ( 'Customer_Feedback' ),
                        [brand] = EARLIER ( Customer_Feedback[brand] )
                    )
                ),
                ,
                DESC,
                DENSE
            )
    )
RETURN
    MAXX (
        FILTER (
            _a,
            [brand_type] = "Own"
                && [customer] = SELECTEDVALUE ( Customer_Feedback[customer] )
        ),
        [Rank]
    )

Final output:

Best Regards,

Jianbo Li

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

 

 

sdrfghjkl's avatar
sdrfghjkl
Frequent Visitor
3 years ago

Sir, 

 

The above solution doesn't work. It doesn't take the customer into account and hence it ranks the brands across all customers. So I tried to tweak the measure slightly and updated the first half of the measure in constructing the dynamic table. 

 

But the problem is the ranking is still wrong. If you see the below picture the first two brands have differnt ratings but are still ranked as 1 

 

 

Complete measure 

Rank =
VAR _a =
     ADDCOLUMNS (
        ALL ( Customer_Feedback[customer],Customer_Feedback[brand],Customer_Feedback[brand_type]),
        "Rank",
            RANKX (
                ALL (Customer_Feedback[brand] ),
                CALCULATE (
                    AVERAGE ( Customer_Feedback[rating] ),
                    FILTER (
                        ALL (Customer_Feedback[brand]),
                        [brand] = EARLIER ( Customer_Feedback[brand] )
                    )
                ),,
                DESC,
                DENSE),
                "Rating",
                CALCULATE (
                    AVERAGE ( Customer_Feedback[rating] ),
                    FILTER (
                        ALL (Customer_Feedback[brand]),
                        [brand] = EARLIER ( Customer_Feedback[brand] )
                    )
                )
            )
RETURN
    MAXX (
        FILTER (
            _a,
            [brand_type] = "Own"
                && [customer] = SELECTEDVALUE ( Customer_Feedback[customer] )
        ),
        [Rank]
    )