Link to home
Start Free TrialLog in
Avatar of Andy Donnelly
Andy DonnellyFlag for United States of America

asked on

Creating a calculated column from two tables in Power BI

I'm having difficulty creating a calculated column.  I've attached the table layout for the three tables in the visualization.   I tried the following calculated column statement:
ResponseRate = (GiftsPerSegment[NumberOfDonors])/RELATED(InitialMailingCounts[TotalMailingCount])
 and I get an error:
"The column 'Initial Mailing Counts[TotalMailingCount] either doesn't exist or doesn't have a relationship to any table available in the current context.  I'm not sure why since the two tables are joined.  Any help would be greatly appreciated.User generated image"  
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

What would happen if you changed TotalMailingCount to a measure?  Then did the divide?  Also what is the relationship between SegmentAnalysis2.1?  One to many?  Which side is the one?
Avatar of Andy Donnelly

ASKER

I can't seem to make TotalMailingCount a measure as it doesn't seem to recognize it yet I can add it to a visualization.  It's based on a different table than the other fields.  

The GiftsPerSegment table is based on results from the mailing in our database, the TotalMailingCount is from the mail house stats on the number of pieces mailed.  They are only related by the SegmentAnalysis2.1 field.

On the tables the 1 side is on the InitialMailingCounts table.

I'm relatively new to Power BI maybe I have something fundamentally wrong?



Related will not work with tables which have Many to Many relationship between them. Related normally works on table with Many side fetching the related values from the dimension table.
I was able to fix the join to be 1 to many. Unfortunately, it still doesn't recognize the InitialMailinCounts fields when I try to create the column.  User generated image
What error you get after fixing the relationship?
Are you sure that you are creating a calculated column on Many side?
Can you upload the pbix file?
After a little more testing I was able to add a measure and use it in the calculation but I still have a couple of issues. 

When I add the TotalMailCount to the table visualization it doesn't display a grand total on the bottom of the column.  Also the response rate is a sum of the values above when it should really be TotalNumberofDonors/TotalMailingCount.

Is there a way of changing the total row for individual columns?

User generated image

What is the formula for the Response Rate?  And how did you determine your TotalMailingCount?


ResponseRate = [NumberOfDonors]/RELATED(InitialMailingCounts[TotalMailingCount])


Total mail count was just a table with the number of pieces mailed.
Your Response Rate calculation % does not look right.  The percentages are slightly off.
The problem of making the % a calculated column is why you have the percentage total at the bottom of your visual.  The % calculation should be a measure. It would be beneficial if you uploaded the file as Subodh suggested.


Sure, sorry I missed uploading it. I was able to get the TotalMailCount grand total to display but haven't made any progress on the ResponseRate detail or grandtotal.


CASAMailFileAnalysis.pbix
ASKER CERTIFIED SOLUTION
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the points.  Glad to help...