cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 262 members 2,914 guests
Please welcome our newest community members: