cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AZL Frequent Visitor
Frequent Visitor

Calculated Column from Related Tables

Hello Everyone,

 

I need your help with this , guys.

 

Lets say I have the following dataset:

 

Capture1.JPG

 

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:

 

Capture2.JPG

 

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
affan Established Member
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
affan Established Member
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

 

AZL Frequent Visitor
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??

affan Established Member
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
Super User

Re: Calculated Column from Related Tables

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 379 members 3,753 guests
Please welcome our newest community members: