Struggling with Many to 1 Solution

[lstein8541Participant Started: 2Replies: 12Total: 14]

I recently read the solution to Many2Many, but trying to solve for a Many to 1 issue.  Anyone with a thought on how to do this?

The setup:

2 tables

1) Contains information about requisitions, and Requisition # is the key and it is unique (this is the “one” in the many to 1).  It contains information around the requisition.

2) Contains information on Applicants, it to has the requisition number contained, however the ‘application’ number is its unique key and is not referenced in the Requisition. And of course many people apply to one requisition, referencing the requisition number multiple times in the table.

I can easily tie the Applicant table to the Requisition and relate the applicant info to the requisition table, however when I get to the pivot, what I am seeking to do list out all the applicants under the requisition number.  My calculated fields are in the requisition table using the related as needed from the applicant table.

I am not looking to sum or even calculate, just list…

So my ideal pivot would be:

requisition #

Applicant #1

Applicant #2

 

Any thoughts how to do this… ?

Any help is very much appreciated !

Larry

PS Love the book, use it every week it seems…

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Larry,

I think I know the issue, you have created a pivot table and dropped Requisition # and Application # on the rows drop zone, and (while Excel/PowerPivot is waiting for you to select a field for the values drop zone) it is listing all application #’s under each requisition #. But you don’t want to calculate anything…

Let me know if I have this right, and if so, I can post a sample workbook. Do you have Excel 2010 or 2013?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Larry,

Attached is a sample Excel 2013 workbook, showing a listing as you described. Note that column C is hidden.

Attachments:

You must be logged in to view attached files.

[lstein8541Participant Started: 2Replies: 12Total: 14]

Thank you for the reply, and for taking the time to make a sample sheet.

Yes you have replicated the data correctly and have done the relationship the same (oh yes, I am on 2013 as well).

I have successfully used your solution before in the past on some fairly complex data.

This application is a tad different.

It is gathering its data from flat files that come in several times a day, so I have linked the data via Power Query (allowing some ETL manipulations) and loading directly into the Power Pivot tables, allowing for “One Button” updating.

My struggle with model of: Power Pivot the data into an Excel sheet, than replicating (I use simple =[CollumnCell], than bringing back into another Power Pivot Table by linking…. is I haven’t found a way to auto adjust the “Coped” cell formulas to expand and reduce as the data does.  So a human has to go into the excel side and delete or copy the formulas to match the size of the data.

Have you found a way around this ?  I would guess VB could do this, but unfortunately, that’s skill I have not mastered…

Right now I am playing with using the “one way” relationship I have, to put in lots of =related(tablename.[fieldname]) in my Applicants table.

For the pivot, things with numbers I use average (which is not precise), for text, it works ok.

Thoughts?

Thanks Again.

 

 

 

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Larry,

There may be a way of achieving your goal using linkback table(s), a feature that Power Pivot for Excel 2013 has (but Excel 2010 does not). I first considered using a linkback table, but did not know then whether you had version 2010 or 2013. Do you have some sample data that I could use to evaluate this option. The data can be fictitious (names and quantities replaced), but the structure of the model should be consistent with the “real” model.

If you would like to read-up on linkback tables, the following link is a great place to start:

http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

Let me know where you want to go from here.

[lstein8541Participant Started: 2Replies: 12Total: 14]

Thank You (Very Much!).

I never knew this existed.

After a bit of readying, I will do some trial (and error) and see if I can get this to work.

Thanks again… Another Day… Another Dax feature to explore!

Larry

[lstein8541Participant Started: 2Replies: 12Total: 14]

After much playing, I discovered linkback can not be used if the original data was brought in via Power Query…

So think I back to Pivoting the data, copy the pivot, import the data back to PowerPivot table.

The downside of this is that it requires a manual intervention to ensure the sheet with the “copy of the pivot” adds or deletes rows each time the data is refreshed.

Thanks for all the efforts !

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Larry,

I have used linkback when the original data was brought directly into PowerPivot via Power Query.

Earlier experience, similar to yours, was more along the lines of I imported (saved and loaded) into an Excel worksheet and also into Power Pivot.

If you have some sample data, it does not have to be (and should not be in this case), real data. But the structure of the model and how you are importing is important. It probably would only need to be a few rows in each table.

Would like to give it a try.

[lstein8541Participant Started: 2Replies: 12Total: 14]

Thanks for all the help.

As soon as I finish this project (couple of days) I will obfuscate the data and send along.

As long as I have you… quick question… need to get to an average of a row (excluding blanks and 0’s) summarized by the unique values of another row.  Tried calculate(summarize(table[column] but no luck.

so Column A has the job codes and column B has how many people applied  to the posting, need the average number of all the postings with the same job code.  Looking to than take that number and apply it to an open posting with the same job codes to try and predict how many people might apply.  Of course from there, I’ll apply geography, and what ads are running etc to see if we can find what marketing works in what geography to increase the number of folks applying….

Thanks !

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Try something like:

Average with Criteria :=
CALCULATE (
AVERAGE ( JobCodeApplicants[Applicants] ),
JobCodeApplicants[Applicants] <> 0
)

JobCodeApplicants is the table name and Applicants the column that contains the quantity. If you put the the Job Codes on rows (and the measure above in values), Power Pivot will figure out the rest

[lstein8541Participant Started: 2Replies: 12Total: 14]

Thanks for responding !   I cant use the  Calculate(Average…, as the key is a text (string).

I tried a combo of functions, but couldn’t find a way to get it work…

Can I do some combo of

Calulate(Average….. Countrows(distinct(table[applicant])), ??

 

Thanks for all your help !

[Author]

Posts