Calculate the Average of Max Sales by Position

[itsme]

I am trying to get the highest sales value (MAX ( 'Sales Table'[Amount] )) for each sales person ('Sales Person'[employeeId]), regardless of the filtered dates. There will always be a page level filter for the product (Product[ProductId]) and attribute (Attribute[AttributeId]), so we want to make sure the max amounts reflects these slicers. I believe my measure for this works fine:

CALCULATE(

        MAX( 'Sales Table'[Amount] ),

        ALL( 'Sales Table' ),

        VALUES( 'Sales Person'[employeeId] ),

        VALUES( Product[ProductId] ),

        VALUES( Attribute[AttributeId] )

    )

Now I need to find the average of each person's maximum sales by position ('Sales Person'[Position]). I tried a few things, like:

AVERAGEX(

    VALUES( 'Sales Person'[Position] ),

    CALCULATE(

        MAX( 'Sales Table'[Amount] ),

        ALL( 'Sales Table' ),

        VALUES( 'Sales Person'[employeeId] ),

        VALUES( Product[ProductId] ),

        VALUES( Attribute[AttributeId] )

    )

)

and

AVERAGEX(

    SUMMARIZE(

        'Sales Person', 'Sales Person'[employeeId],

        "maxSales", CALCULATE( MAX( 'Sales Table'[Amount] ), ALL( 'Sales Table' ), VALUES( 'Sales Person'[employeeId] ), VALUES( 'Product'[ProductId] ), VALUES( Attribute[AttributeId] ) )

    ),

    [maxSales]

)

The last measure seems to be working fine when I put it into a table with Position, but once I put the Person into the table it shows that person's max sales amount. I need it to show the average of everyone's max sales who is in the same position as the person in that row, for the selected product and attribute selected (page level filters in Filter Pane).

I feel like I'm so close! Can anyone help please?

Thank you!

[DataInsights]

@itsme,

Try the measure below. This assumes you have a star schema (each dimension table has a 1:M relationship with the fact table "Sales Table"). Notice the filters in lines 20-23; these are necessary to prevent the visual from slicing by employeeId when employeeId is in the visual. You do, however, need employeeId in SUMMARIZE so you can calculate the max amount for each employeeId. Recap:

1. Create virtual table vMaxAmountByPerson consisting of Position, employeeId, and MaxAmount. This virtual table ignores filter context for employeeId provided by the visual (e.g., matrix row).

2. Create virtual table vMaxAmountByPosition consisting of Position and MaxAmount (remove employeeId from vMaxAmountByPerson).

3. Average MaxAmount in vMaxAmountByPosition.

Average Max Amount by Position =
VAR vMaxAmountByPerson =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Sales Table',
                'Sales Person'[Position],
                'Sales Person'[employeeId]
            ),
            "@MaxAmount",
                CALCULATE (
                    MAX ( 'Sales Table'[Amount] ),
                    ALL ( 'Sales Table' ),
                    VALUES ( 'Sales Person'[employeeId] ),
                    VALUES ( Product[ProductId] ),
                    VALUES ( Attribute[AttributeId] )
                )
        ),
        // remove employeeId filter while keeping filters for Position, ProductId, and AttributeId; this allows you to add employeeId to a visual without affecting the result
        ALL ( 'Sales Table' ),
        VALUES ( 'Sales Person'[Position] ),
        VALUES ( Product[ProductId] ),
        VALUES ( Attribute[AttributeId] )
    )
VAR vMaxAmountByPosition =
    SELECTCOLUMNS (
        vMaxAmountByPerson,
        "Position", 'Sales Person'[Position],
        "@MaxAmount", [@MaxAmount]
    )
VAR vResult =
    AVERAGEX ( vMaxAmountByPosition, [@MaxAmount] )
RETURN
    vResult

[itsme · Accepted solution]

Thanks @DataInsights . That did not work, it was still showing the max sales per employee when employee was in the filter context (row of table visual). My colleague helped me figure it out though. Here is the DAX code that works:

Average Max Amount by Position = 
IF (
    NOT ISEMPTY ( 'Sales Table' ),
    AVERAGEX (
        VALUES ( 'Sales Person'[Position] ),
        AVERAGEX (
            CALCULATETABLE (
                VALUES (  'Sales Person'['Sales Person'] ),
                ALL ( 'Sales Person'['Sales Person'] )
            ),
            CALCULATE ( MAX ( 'Sales Table'[Amount] ), ALL ( 'Date Table' ) )
        )
    )
)

Also worth noting, I forgot to mention earlier that the measure should ignore any date filters when calculating the average max amount by position, hence the use of ALL ( 'Date Table' ) in CALCULATE at the end of the measure.

Another note, in the Totals line, the output is the average of all the selected position averages, not the average of all selected employee maximums, if that makes any sense.

Thanks!