Average of the max by category

[zanottiluca]

Hi guys, 

spent lot of time looking at a similar issue, but cannot find anything. 

As per below i have a visual listing for each module and each ccategory the best mark (using BestMark = Maxx(Marks,Marks[mark]) ) because there may be several attempts but the visual should show the best. Now i need to set the average of these best results per category. For instance on the below for the first two values should show 6, for the next 3 should show 5.33 etc. 

I have tried summarize because i have read that it should act as a group by, but no joy. For each row i am getting the same result. I encoounter the same behaviour if i would like to do a Maximum of these best results.

I may achieve the goal by creating some tables in Edit query but i believe DAX would be faster.

Any suggestion is really much appreciated. 

 

Thanks a lot

Luca

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The short answer is to create a temp table in your measure using SUMMARIZE.

[zanottiluca]

Hi Greg, 

thanks for the post and link, the problem i am facing is that CatName (or your Attribute) is on the same table as my marks (or your score) and if i follow the pattern described i would have a different result for each rows as per below. 

Here are the measure created

BestScore2 = CALCULATE(max(Marks[mark]),values(Marks[CatName]))

MeanScoreSummaarize = averagex(SUMMARIZE(Marks,Marks[CatName],"maxscore",[BestScore2]),[maxscore])

Do you think i need to create a reference table whih stores the category in Edit Query, or there is another way? 

Thanks

[Greg_Deckler]

So, I'm not entirely clear what what you are going for here. I re-read the original post several times and I'm missing something. Can you post some sample of your original data in a form that can be copy and pasted and then post what you want the results for that sample data to look like when you are done?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[zanottiluca]

Hi Greg,

apologies if i confuse you and thanks for the reply.

here is the link to my pbix https://1drv.ms/u/s!AjU3fZfEMvkyhaEA66iEKANFU7eDMA

and file https://1drv.ms/x/s!AjU3fZfEMvkyhaB_opG_JjMawK3bmA 

As you can see my aim is to create the average of the best score. 

the final result should be: 

id     module catname  bestscore avgbestsore maxbestscore

1          english    signs        5                 6                 7

2          english    signs        7                 6                 7

1          math       number    2                5.33             10          

2          math       number    4                5.33             10

3          math       number    10              5.33             10 

I have tried to use the summarized but the result in the visual change for each of the record.

Thanks

Luca

[v-shex-msft]

Hi @zanottiluca,

Summarize function not suitable for your scenario, you only add one category column, so it only return one summarized row per cat name.

You can try to use below formula if it works on you side:

MeanScoreSummaarize = 
VAR currnet_name =
    SELECTEDVALUE ( Marks[CatName] )
RETURN
    MAXX ( FILTER ( ALLSELECTED ( Marks ), [CatName] = currnet_name ), [mark] )
        / CALCULATE (
            COUNTROWS ( Marks ),
            ALLSELECTED ( Marks ),
            Marks[CatName] = currnet_name
        )

Regards,

Xiaoxin Sheng