cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Turning Mode measure into a column

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

1 ACCEPTED SOLUTION
Super User

Hi @ase

``````=
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] )``````
2 REPLIES 2
Frequent Visitor

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

Super User

Hi @ase

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors