Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?!
Solved! Go to 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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |