topic Re: Create a measure to get year corresponding to peak sales sum in DAX Commands and Tips

Create a measure to get year corresponding to peak sales sum

Anonymous — Tue, 01 Nov 2022 10:40:16 GMT

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

Re: Create a measure to get year corresponding to peak sales sum

Jihwan_Kim — Tue, 01 Nov 2022 10:51:36 GMT

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

Re: Create a measure to get year corresponding to peak sales sum

Anonymous — Tue, 01 Nov 2022 13:29:06 GMT

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

Re: Create a measure to get year corresponding to peak sales sum

Jihwan_Kim — Tue, 01 Nov 2022 14:10:32 GMT

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