Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

View solution in original post

4 REPLIES 4
affan
Solution Sage
Solution Sage

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

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.