cancel
Showing results for
Did you mean:
Helper I

## Adding new columns (calculations) to existing matrix

Hello, this is what the data originally looked like:

 Name Type Value Person 1 Height 187 cm Person 1 Weight 76 kg Person 2 Height 165 cm Person 2 Weight 63 kg

I have changed these to the following by placing them in a matrix:

 Name Weight Height Person 1 187 cm 76 kg Person 2 165 cm 63 kg

What I am trying to do now is to add a new column to this matrix. I would like a column that calculates the BMI. But I cannot manage to call the weight and height columns in my formula, perhaps because they are not "real columns". Anyone have any idea how I can solve this?

 Name Height Weight BMI Person 1 187 cm 76 kg X Person 2 165 cm 63 kg Y

Thanks for helping me out!

1 ACCEPTED SOLUTION
Super User

@avatar456 Try creating a measure like:

``````Measure =
VAR __Height = MAXX(FILTER('Table',[Type] = "Height"),[Value])
VAR __Weight = MAXX(FILTER('Table',[Type] = "Weight"),[Value])
RETURN
//BMI Calc goes here.
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Helper I

New rows have been added to my dataset, in addition to height and weight, there is now a type for residence. This creates a text in the 'Value' column. Because this column is now a mix of numbers and text, I can no longer filter my height, weight and BMI.
Can I specify in the formula whether it is text or numbers?

``Cannot convert value " of type Text to type Number. ``

Any suggestions? Thanks voor helping me out!

Super User

@avatar456 Try creating a measure like:

``````Measure =
VAR __Height = MAXX(FILTER('Table',[Type] = "Height"),[Value])
VAR __Weight = MAXX(FILTER('Table',[Type] = "Weight"),[Value])
RETURN
//BMI Calc goes here.
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

Thanks, the calculation of my new column is now correct. But when I add it to my visual, the new column appears there multiple times (with each type). Any idea how I can solve this?

Super User

@avatar456 Right, so you have a few options. Option one is to use Name as Rows and create separate measures for Height, Weight and BMI like: Height Measure = MAX('Table'[Height]). Use these measures in your Values and don't use anything for Columns.

Another option is to turn off word wrap and shrink your unwanted columns.

Fourth option is to switch to a Table visual instead of a Matrix.

I would vote for the 1st or 4th options personally.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

@Greg_DecklerThanks, this was exactly what I was looking for!

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors