Max Function Aggregate

[Altonga]

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%.

[Anonymous]

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

[tamerj1]

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%] )
)
)
)
)

[Altonga]

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?

[tamerj1]

@Altonga 

Please share a screenshot?

[Altonga]

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!

[tamerj1 · Accepted solution]

@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%] )
)
)
)
)