Two level chain of context/measures with DAX

[Anonymous]

Hello,

I have following scenario: a few persons attend to various events spread over yearly period. Each person is rewarded by a number of points for each event, the number can vary depending on the event.

The target consists in calculating the percentage of points for each candidate compared to the maximum possible during the given period. The maximum possible is defined by the sum for all events of the highest number of points obtained by the best performer for the local event. In case two candidates are best performers of course only one value shall be counted in the sum. See illustration for an example.

The maximum by event can be obtained by DAX command as shown in the bottom of the picture. The sum of the max by period is more difficult because of the complexity of combining change of context and measure of a measure. What is the way to go here?  

The constraint is to do it with DAX, and let the door open to have user interactivity on period, event and name selection.

Thank you for your support and have a nice day

Input data:

Period     Event      Name    Amount

FY21	aa	Paul	10
FY21	aa	John	10
FY21	aa	Philipp	4
FY21	bb	Philipp	12
FY21	bb	John	8
FY21	bb	Paul	4
FY21	bb	Roger	2
FY20	aa	Roger	8
FY20	aa	John	15
FY20	aa	Philipp	4
FY20	bb	Paul	14
FY20	bb	Roger	8
FY20	bb	John	2

[daXtreme]

Here's a solution that uses very short and sweet measures and highlights things on the visuals... You can do the rest EASILY yourself 🙂 The measures are relative to what's visible in the visuals. Play with this to see for yourself.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _numerator = [Amount measure:]
VAR _denominator =
    SUMX (
        GROUPBY (
            ADDCOLUMNS (
                SUMMARIZE (
                    CALCULATETABLE ( Data, ALL ( 'Name' ), ALL ( Event ) ),
                    Event[Event],
                    'Name'[Name]
                ),
                "@amount", CALCULATE ( SUM ( Data[Amount] ) )
            ),
            Event[Event],
            "@maxbyevent", MAXX ( CURRENTGROUP (), [@amount] )
        ),
        [@maxbyevent]
    )
RETURN
    DIVIDE ( _numerator, _denominator )