Adding new columns (calculations) to existing matrix

[avatar456]

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!

[avatar456]

@Greg_Deckler  

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! 

[Greg_Deckler · Accepted solution]

@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.

[avatar456]

@Greg_Deckler 

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?

[Greg_Deckler]

@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. 

Third option is: The New Hotness (Custom Matrix Hierarchy) - Microsoft Power BI Community

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

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

[avatar456]

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