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

Highlighted
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: 6 members 2,672 guests
Recent signins: