Andy Donnelly
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."
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."
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?
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?
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.
ASKER
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?
Are you sure that you are creating a calculated column on Many side?
Can you upload the pbix file?
ASKER
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?
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?
What is the formula for the Response Rate? And how did you determine your TotalMailingCount?
ASKER
ResponseRate = [NumberOfDonors]/RELATED(InitialMailingCounts[TotalMailingCount])
ASKER
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.
ASKER
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
CASAMailFileAnalysis.pbix
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points. Glad to help...