Summarize and Ranking

[Anonymous]

Hi All,

I am trying to write a dax to rank the Top3 Model based on there Trust Value.

Here the Trust is Avg Value By Model across all Cluster so i get duplicate rows and hence i thought i will use Summarize to Group the Table by Cluster, Model and Trust and pick top3. I get an error though using the below dax 
"Multiple column Cannot be convereted to scalar value"

Model	Cluster	AvgtrustByModel
Arima	A	50
Arima	B	50
Arima	C	50
Ets	A	40
Ets	B	40
Ets	C	40
Prophet	A	60
Prophet	B	60
Prophet	C	60

TOP3Model1 = 
FILTER(SUMMARIZECOLUMNS('Submission KPIs'[Cluster_long], 'Submission KPIs'[Model_Name], "AvgRankByModel", AVERAGE('Submission KPIs'[Trust] )),
CONCATENATEX(
        TOPN(
            3
            ,'Submission KPIs'
            ,[AvgRankByModel]
            ,DESC
        )
        ,'Submission KPIs'[Model_Name]&"("&FORMAT([AvgRankByModel],"##%")&")"
        ,", "
        ,[AvgRankByModel]
    ))

[Anonymous]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous That code is returning a table and measures have to return scalar values. Try this:

TOP3Model1 = 
  VAR __Table = SUMMARIZECOLUMNS('Submission KPIs'[Cluster_long], 'Submission KPIs'[Model_Name], "AvgRankByModel", AVERAGE('Submission KPIs'[Trust] )
RETURN
CONCATENATEX(
        TOPN(
            3
            ,__Table
            ,[AvgRankByModel]
            ,DESC
        )
        ,'Submission KPIs'[Model_Name]&"("&FORMAT([AvgRankByModel],"##%")&")"
        ,", "
        ,[AvgRankByModel]
    ))

[Anonymous]

Thanks @Greg_Deckler , i made a change to it and it worked. Can you also help me how can i reverse the order of the result. Current Ouptut is ETS(40%) ,Arima(50%), Prophet(60%), i think its because of the FORMAT String. How can i make it output display as  Prophet(60%), Arima(50%), ETS(40%)? 

[Anonymous]

Hi @Anonymous,

Perhaps you can try to modify the sort parameter DESC to ASC if it help with your requirement.

Regards,

Xiaoxin Sheng