Create a measure to get year corresponding to peak sales sum

[Anonymous]

We have two input tables and I want to return a grid as shown below. The tables are related based on the Product column via one to many relationship.  The year corresponds to the maximum sales sum for an Asset.

Example : for A1 the max sales sum is 90 so the year corresponding to that is 2022

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Year of max sales sum: =
VAR _newtable =
    ADDCOLUMNS (
        DISTINCT ( Table2[Year] ),
        "@salessum", CALCULATE ( SUM ( Table2[Sales] ) )
    )
VAR _maxsales =
    GROUPBY (
        _newtable,
        Table2[Year],
        "@maxsales", MAXX ( CURRENTGROUP (), [@salessum] )
    )
RETURN
    IF ( HASONEVALUE ( Table1[Asset] ), MAXX ( _maxsales, Table2[Year] ) )

[Anonymous]

Thanks! But this not render the correct result for A1, it should be 2022

[Jihwan_Kim · Accepted solution]

Hi,

Sorry for the mistake.

Please kindly check the below whether it suits your requirement.

Year of max sales sum: =
VAR _maxsales =
    MAXX ( SUMMARIZE ( Table2, Table2[Year], Table2[Sales] ), Table2[Sales] )
VAR _maxsalesyear =
    FILTER (
        SUMMARIZE ( Table2, Table2[Year], Table2[Sales] ),
        Table2[Sales] = _maxsales
    )
RETURN
    IF ( HASONEVALUE ( Table1[Asset] ), MAXX ( _maxsalesyear, Table2[Year] ) )