Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |