Calculate Max value from a calculated measure per category

[Fair-UL]

Hi,

I have this problem and tried/searched several solutions, nothing worked

project id	section	items	sum of responses	max score	Percent rating
6	s1	5	15	45	33.33%
6	s2	6	7	54	12.94%
6	s3	2	0	18	0%
7	s1	2	0	18	0%
7	s2	6	10	54	18.52%
7	s3	9	0	81	0%

Max score= Items*9 (i have it as calculated measure)

sum of responses= sum(response)- the response is a field, each instance has a value, but I put it here to explain how I want data to look like

Percent Rating= Sum of Responses/Max Score

I want to be able to calculate the Maximum of Percent Rating per each category (section). Results should look like

section	Max of percent per section
s1	33.33%
s2	18.52%
s3	0%

I tried quick measure Max per category, not giving me what I need when I filter by each section. I tried many DAX statements, with calculate, all, allselected, no luck

all help is appreciated

[amitchandak]

@Fair-UL , Assuming Percent rating is a measure,

Try a measure like, and plot with a section in a visual

maxx(values(Table[section]),[Percent rating])

[Fair-UL]

I tried the solution you provided. It gave me the same results as the other two DAX calculations I used

here are those dax

Max of Percent =

MAXX(

KEEPFILTERS(VALUES('Table'[section])),

CALCULATE([Percent Rating])

)

then I calculated this measure=

calculate([Max of Percent],ALL('SAFETYAUDITS Responses'[project_id]))

[v-lionel-msft]

Hi @Fair-UL ,

The table visual seems to automate this aggregation work.

Best regards,
Lionel Chen

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

[Fair-UL · Accepted solution]

True. The table aggregates the data as I need but when I filter by Project ID it does not keep the max value that has been calculated to an instance.

To solve this I used the GROUPBY function to crteate a new table and have the Max score and percent rating be calculated columns. then used the expression= calculate(maxx(percent rating),all(project Id)). I put it on the graph and filtered by audit id and it produced what I needed. Thank you @v-lionel-msft @amitchandak

[Anonymous]

Hello,

I have the same problem but I don't understand the fix using the GROUPBY function. Is it possible to be more explicit ?

Thank you for your help.