cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated Column from Related Tables

Hello Everyone,

I need your help with this , guys.

Lets say I have the following dataset:

Table1 & Table2 are linked together through Col1 (Many to Many relation)

I want to create a new calculated column in Table 2 that will do the folloiwng for each value in Table 2 ,Col1:[ Sum (Col2,Table 1) / Col2,Table2] * 100%

Accordingly, the calculated column (Col3) will look like this:

Note: I dont know why the defined relation is (Many to Many) even though all values in Col1 , Table2 are unique and not repeated!!!!!!!

Is it because of empty cell or null?!

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Calculated Column from Related Tables

Hi @AZL

You can use the below measure, it is having the check for blanks in the IF statement in the result area.

```Col3 = var _sumcol2=CALCULATE(SUM(Table1[Col2]),FILTER(Table1,Table1[Col1]=Table2[Col1]))
var _T2Col2=Table2[Col2]
var _result=_sumcol2/_T2Col2*100
Return
IF(ISBLANK(_result),0,_result)```

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

4 REPLIES 4
Highlighted
Established Member

## Re: Calculated Column from Related Tables

Hi @AZL

Your guess about the many to many seems to be 100% accurate, it is because of null or blank values in the table.

You can remove the null values in use the below DAX to create a column

```Col3 = var _sumcol2=CALCULATE(SUM(Table1[Col2]),FILTER(Table1,Table1[Col1]=Table2[Col1]))
var _T2Col2=Table2[Col2]
Return
_sumcol2/_T2Col2*100```

You can see the attached pbix file.

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

Frequent Visitor

## Re: Calculated Column from Related Tables

Thank you so much for the solution.It works well with me.

But I have one more porblem. As you notice in attached file the values for "D & E & F" are empty eventhough it should be zero (since there's no value for these items in Table 1).How can I fix this problem??

Established Member

## Re: Calculated Column from Related Tables

Hi @AZL

You can use the below measure, it is having the check for blanks in the IF statement in the result area.

```Col3 = var _sumcol2=CALCULATE(SUM(Table1[Col2]),FILTER(Table1,Table1[Col1]=Table2[Col1]))
var _T2Col2=Table2[Col2]
var _result=_sumcol2/_T2Col2*100
Return
IF(ISBLANK(_result),0,_result)```

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

Super User

## Re: Calculated Column from Related Tables

Hi,

Hope this helps.

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 262 members 2,914 guests
Recent signins: