topic Re: Max Function Aggregate in DAX Commands and Tips

Max Function Aggregate

Altonga — Thu, 05 Jan 2023 13:55:01 GMT

Hello all,

I'm having some issues with a training tracker that I am building, I want to track progress of students from 'tests' that they carry out.

Test1 = 10%

Test2 = 20%

Test3 = 30%

Test4 = 20%

Test5 = 20%

Total = 100%

Each subject has numerous subcategories within it which build up to an overall percentage within this subject, one issue is that the students can complete the same tests multiple times and I only want to identify one entry so I have used the below MAX fuction:

%Trained =

MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] )

+ MAX ( 'Results'[Test3%] )

+ MAX ( 'Results'[Test4%] )

+ MAX ( 'Results'[Test5%] )

This worked to gain the correct percentage for one individual subcategory but when it came to aggregating over the numerous subcategories the max function is throwing off the calculation. This is what I tried to use:

%TrainedAggregate = Divide(

MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] )

+ MAX ( 'Results'[Test3%] )

+ MAX ( 'Results'[Test4%] )

+ MAX ( 'Results'[Test5%] ) , [CountSubjectSubcategory])

I have changed the information due to sensitivity, The first image as you can see gives the figure I would expect, on the second however when I am aggregating over 12 subcategories it is only picking up 100% as the max when in theory it would be 1200%.

Re: Max Function Aggregate

tamerj1 — Thu, 05 Jan 2023 14:22:56 GMT

Hi Altonga

assuming you need to sum over subcategory and average ober subjects:

%Trained =
AVERAGEX (
VALUES ( 'Results'[Chapter] ),
CALCULATE (
SUMX (
VALUES ( 'Results'[Subchapter] ),
CALCULATE (
MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] )
+ MAX ( 'Results'[Test3%] )
+ MAX ( 'Results'[Test4%] )
+ MAX ( 'Results'[Test5%] )
)
)
)
)

Re: Max Function Aggregate

Altonga — Thu, 05 Jan 2023 14:59:25 GMT

Hi tamerj1, thank you for your reply, this is so close to the desired outcome! So it is now displaying correct percentages for all of the subchapters added up but it isn't showing the aggregated value. Is there a way to add to this code to divide by count of subchapters selected?

Re: Max Function Aggregate

tamerj1 — Thu, 05 Jan 2023 15:04:04 GMT

Altonga

Please share a screenshot?

Re: Max Function Aggregate

Anonymous — Thu, 05 Jan 2023 15:23:08 GMT

Can you try to use (show value as Percent of Grand Total) in the Visualization.

Re: Max Function Aggregate

Altonga — Thu, 05 Jan 2023 15:34:46 GMT

Hi,

As you can see below for the first student 120% is correct for how much they have scored over all of the subcategories but I am after an aggregation of this so in theory Student one would be displaying 10%. Apologies as my knowledge isn't very extensive but hoping it would be an easy fix!

Re: Max Function Aggregate

tamerj1 — Thu, 05 Jan 2023 15:42:48 GMT

Altonga

Please try

%Trained =
AVERAGEX (
VALUES ( 'Results'[Chapter] ),
CALCULATE (
AVERAGEX (
VALUES ( 'Results'[Subchapter] ),
CALCULATE (
MAX ( 'Results'[Test1%] ) + MAX ( 'Results'[Test2%] )
+ MAX ( 'Results'[Test3%] )
+ MAX ( 'Results'[Test4%] )
+ MAX ( 'Results'[Test5%] )
)
)
)
)