Forum Discussion
join calculated tables?
Hi,
I've created two calculated tables: One to summarize questions received by students (screenshot):
And one to summarize questions answered (screenshot):
In order to calculate the percentage answered in the second calculated table I need to use the values in the first calculated table
I tried merging the question and responses tables first, as a couple people mentioned is probably best practice, but it wasn't coming out correctly, more rows than either original summarized calculated table, as if the merge was not filtering correctly. Stuff that people hadn't "received" was being marked as "responded" (I think non-questions, even with the filter I describe below) What finally worked:
Merging comm type from communications table into the responses source table, and filtering on equals = question (I had already filtered out Package ID-less responses).
Keeping the summarized tables, Questions Received and Questions Answered.
Using the related function to create a column for Total Replied in the Questions Received table. Then calcuating the percent.
Thanks everyone!!
7 Replies
Sorry if I'm missing it, what is the question actually? Which column are you trying to calculate or join? When you say 'Join', do you mean creating a relationship?
You would just create a relationship using the Package ID as the key column.
Thanks for looking at this. Looks like my full explanation was cut off when I posted. Not sure what happened there.
I am trying to create a calculated column in a calculated table by dividing a column from one table by a column from another.
Table A: Questions Received
Column 1: Package ID Column 2: Total Received Column 3: Percent Received
1 50 20% [Total Received]/[Student Count] (A calculated measure already done)
2 12
3 3
4 40
These columns tell me how many people received each question. They are calculated using SUMMARIZE from a query table.
Table B: Questions Answered
Column 1: Package ID Column 2: Total Replied Column 3: Percent Replied
1 5 ?? (How do I get this? I know it is Total Replied/Total Received)
2 3
4 2
These columns tell me how many people answered the question. They are calculated the same way as in Table 1, but the source table is different.
I need to say for each question, what percent of people replied. So, I need Total Replied/Total Received. For question 1, that would be 5%.
I cannot do a straight calcuated column with Percent replied = 'Questions Answered'[Total Responses]/'Questions Received'[Total Received]. There is a relationship set between the two calculated tables using Package ID. I get an error saying: "A single value for column "Total Received" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation".
I was trying to merge the two tables using NATURALINNERJOIN('Questions Received','Questions Answered), which would take out the questions that weren't answered, but I have those in the source table. I get an error saying: "The column with the name of Package ID (which is what the join is made from) already exists in the table (that I am trying to create in the merge)".
Thanks again!
Betsy
Hi Betsy,
I'm interested in why you have created the calculated tables, can you share what your underlying data looks like?
I have a hunch you may be better off rethinking your data model to achieve what you need and reduce the complexity. I can think of 3 possible ways forward:
- Revise your data model by looking again at how you are designing your overall table structure.
- Similar to 1, keep the two calculated tables but dont join them directly, instead create a third Package_ID table (just a dimension table listing all your packages) and link both your calculated tables to that (and not each other at all). you can then cross reference calculations against each table as long as you use the Package_ID table fields as your dimension (column/row labels).
- If you do need to keep your structure as is you might want to use the related function to created new columns in table A which you can then calculate on more easilly, smethign like related(TableB[total Replied])
I think the simplest thing for you will be to merge the two calculated tables you have into one before doing any other calculations. Can you maybe try doing something like:
DISTINCT(UNION(SELECTCOLUMNS('question table', "Package ID", 'question table'[Package ID]), SELECTCOLUMNS('response table', "Package ID", 'response table'[Package ID])))
to give you all Package IDs in both tables, and maybe something like this for the whole formula:
ADDCOLUMNS(
DISTINCT(UNION(SELECTCOLUMNS('question table', "PackageID", 'question table'[Package ID]), SELECTCOLUMNS('response table', "PackageID", 'response table'[Package ID]))),
"Total Received", COUNTROWS(FILTER('question table', 'question table'[Package ID] = [PackageID])),
"Total Responses", COUNTROWS(FILTER('response table', 'response table'[Package ID] = [PackageID])))
That might make it easier to do calculations bewteen the numbers, and I don't think having two summary tables here really makes sense. Hope that helps!
I tried merging the question and responses tables first, as a couple people mentioned is probably best practice, but it wasn't coming out correctly, more rows than either original summarized calculated table, as if the merge was not filtering correctly. Stuff that people hadn't "received" was being marked as "responded" (I think non-questions, even with the filter I describe below) What finally worked:
Merging comm type from communications table into the responses source table, and filtering on equals = question (I had already filtered out Package ID-less responses).
Keeping the summarized tables, Questions Received and Questions Answered.
Using the related function to create a column for Total Replied in the Questions Received table. Then calcuating the percent.
Thanks everyone!!
