Turning Mode measure into a column

[ase]

Good afternoon,

I have a trouble converting a measure that I created, into a column. Below is the ilustration of the issue: 

I successfully created a measure that calculates the mode for each product - so it shows that the mode for Apples in 2021 is 5.96, but I need it as a column, as shown in the screenshot. 

The column should pick up the Mode amount for each product and sort it per year. Could somebody help me out with this? I read that a solution might be to create a virtual table from the Mode measure and then use lookup, but this sounds too complicated for someone with my skills. 

Thank you very much in advance!

Kind regards,

B

[ase]

It works! Thank you very much for your time and effort!

[tamerj1 · Accepted solution]

Hi @ase 

please try

=
VAR ItemYearTable =
    CALCULATETABLE (
        TableName,
        ALLEXCEPT ( TableName, TableName[Item], TableName[Yea] )
    )
VAR SummaryTable =
    SUMMARIZE (
        ItemYearTable,
        TableName[price per unit],
        "@cont", CALCULATE ( COUNTROWS ( TableName ) )
    )
VAR MaxCount =
    MAXX ( SummaryTable, [@count] )
RETURN
    MAXX ( FILTER ( SummaryTable, [@count] = MaxCount ), TableName[price per unit] )