Dynamic Ranking measure and a card Visual

Question

Hi,&nbsp;&nbsp;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&nbsp;&nbsp;Date&nbsp;brandbrand_typecategorycustomerrating01-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22PARLECompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22CADBURYCompetitionBISCUITSDemo-Biscuits4.201-Sep-22BRITANNIACompetitionBISCUITSDemo-Biscuits4.201-Sep-22PARLECompetitionBISCUITSDemo-Biscuits4.201-Sep-22PARLECompetitionBISCUITSDemo-Biscuits4.201-Sep-22PARLECompetitionBISCUITSDemo-Biscuits4.2&nbsp;I basically need to create a measure that Ranks Each brand based on the average rating and extract the value where brand type = Own.&nbsp; I am trying to construct a Card visual that shows the rank for our own brands of our customers,&nbsp;&nbsp;The hierarchy of ranking should be Customer - &gt; Brands and I need to extract the value of brand rank where brand type = Own.&nbsp;&nbsp;the current measure we have doesn't solve the purpose.&nbsp;Brand Rank = RANKX(ALL(Customer_Feedback[brand]),'Measure'[Avg Rank],,DESC,Dense)&nbsp;I am also leaving a link to the entire sample data if that will help :&nbsp;Sample Data&nbsp;&nbsp;Any help on this will be greatly appreciated.&nbsp;&nbsp;the objective is to show a card visual like this where we show only the own brands rank for each customer:&nbsp;&nbsp;&nbsp;Thanks much!&nbsp;&nbsp;&nbsp;

sdrfghjkl · Answer

jdbuchanan71&nbsp; sir sorry for tagging you directly here. I did see a response from you for a similar problem statement and I used that as a reference too. Tagging to see if you can help with this&nbsp;

v-jianboli-msft · Answer

Hi&nbsp;sdrfghjkl&nbsp;,
&nbsp;
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"
                &amp;&amp; [customer] = SELECTEDVALUE ( Customer_Feedback[customer] )
        ),
        [Rank]
    )

Final output:

Best Regards,
Jianbo Li
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;

sdrfghjkl · Answer

Sir,&nbsp;&nbsp;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.&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;Complete measure&nbsp;Rank = VAR _a =&nbsp; &nbsp; &nbsp;ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; ALL ( Customer_Feedback[customer],Customer_Feedback[brand],Customer_Feedback[brand_type]),&nbsp; &nbsp; &nbsp; &nbsp; "Rank",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RANKX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL (Customer_Feedback[brand] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGE ( Customer_Feedback[rating] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL (Customer_Feedback[brand]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [brand] = EARLIER ( Customer_Feedback[brand] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESC,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DENSE),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Rating",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AVERAGE ( Customer_Feedback[rating] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL (Customer_Feedback[brand]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [brand] = EARLIER ( Customer_Feedback[brand] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )RETURN&nbsp; &nbsp; MAXX (&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; _a,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [brand_type] = "Own"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; [customer] = SELECTEDVALUE ( Customer_Feedback[customer] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; [Rank]&nbsp; &nbsp; )

Forum Discussion

Dynamic Ranking measure and a card Visual

3 Replies

Related content

Dynamic Ranking

Dynamic Ranking

Dynamic Ranking

Dynamic Ranking

Dynamic Rank Calculation

Date	brand	brand_type	category	customer	rating
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	PARLE	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	CADBURY	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	BRITANNIA	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	PARLE	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	PARLE	Competition	BISCUITS	Demo-Biscuits	4.2
01-Sep-22	PARLE	Competition	BISCUITS	Demo-Biscuits	4.2