cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avatar456
Helper I
Helper I

Adding new columns (calculations) to existing matrix

Hello, this is what the data originally looked like:

 

NameTypeValue
Person 1Height187 cm
Person 1Weight76 kg
Person 2Height165 cm
Person 2Weight63 kg

 

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

 

NameWeightHeight
Person 1187 cm76 kg
Person 2165 cm63 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?

 

NameHeightWeightBMI
Person 1187 cm76 kgX
Person 2165 cm63 kgY

 

Thanks for helping me out!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
avatar456
Helper I
Helper I

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

avatar456_0-1664447607829.png

 

 

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

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

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 â__ December 6-8, 2022

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.