Multiplying rows together only if they meet a particular criteria - Working out M2 using two columns

[Anonymous]

Hi All,

Pretty new to Power BI. Basicall I am trying to get a M2 summary using CM of Height and Width. The CM are in a Value column along with a heap of other data. I have a Label ID column that runs along this. 

I have a column that holds multiple label IDs that also hold the IDs for Height and Width. The image below shows 1171 and 1170 as Width and Height in the jobtype_fileld_id column along with the Value (which is in cm). 

I am trying to make a M2 column first of all. This in Excel would be simply making two new fields with the filtered data then multiplying those two fields together then dividing it by 10000. 

Any help would be awsome.
Many Thanks in Advance

[mwegener · Accepted solution]

Hi @Anonymous ,

use Power Query (Transform data) for this.

Mark the field jobtype_fileld_id and press pivot column.

Than calculate your Custom Column

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hi @mwegener,

Thanks for that. It definitely did the job.

However, as it has split up all the rows it has kept the same number of Job ID Rows. So subsequently I can't just go col A * col B. Do you have a quick solution for grouping these together. I tried the "Group" function. But this made separeate tables rather than removing excess rows or compressing the rows to Job Id.

Many Thanks in advance

Rob

[mwegener]

Hi @Anonymous ,

reduce first the query to these three columns job_id, jobtype_field_id and value, 

then you should get only one row for each job_id.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.