Calculated column formula

[abukapsoun]

Hi experts,

Need your support please 

I have the following two table

ColA   ColB   ColC

X          17     P01

X          19     P01

Y          30     P02

Y          15     P02

Z          15     P03

ColAA  ColBB

P01       20

P02       20

P03       25

I want to calculate the % of ColB based on the input of ColBB.. meaning something like ColB/ColBB x 100 

so the end result would like that 

ColA   ColB   ColC

X          90%     P01

X          85%     P01

Y          ..%     P02

Y          ..%     P02

Z          ..%     P03

How can achieve this?

thank you very much in advanced

Thanks

[v-yulgu-msft]

Hi @abukapsoun ,

New a calculated column in the first table.

ColB/ColBB = [ColB]/LOOKUPVALUE(Table2[ColBB],Table2[ColAA],Table1[ColC])

Alternatively, you could establish a one to many relationship between two tables. Then, use below DAX formula.

ColB/ColBB = [ColB]/RELATED(Table2[ColBB])

Best regards,

Yuliana Gu

[abukapsoun · Accepted solution]

Hi,

It worked absolutely fine! Thank you very much. 

Another question please. 

Now I want to display those values, I have used the matrix table to show each period. However the total displayed is the sum of both periods while I am looking to have it as an average. 

 

When I use change the type to average, the values dont look correct. 

What do you suggest to correct it? Maybe if there is even another way without using the matrix table istead

Thank you very much

@v-yulgu-msft 

[v-yulgu-msft]

Hi @abukapsoun ,

Since the original question has been worked out, would you please kindly accept it as an answer so that it can benefit others? For any other question, I would suggest you post a new thread on this forum, so that more community menbers can view it and provide solutions.

Best regards,

Yuliana gu