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

Thank you for the help. After multiple trys using different combinations of fields, I finally gave up, and decided “brawn” was better than “Elegant”. Used a linkback table to count the number of records and than divided it back to get to a single amount. Not ideal, but gets me past todays need. I will have to keep playing to try and get the idea to work, as it will prove very handy in the future.

Thank you for all the help (today and all the other times !)

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

tom,

thanks for the reply.

Pgs 143 is where I started as to how to solve this (and just went back and re-read!).

I tried to do its suggestion (using HASONEVALUE) and sumx using district, but it wont allow me without a measure statement (eg average, sum etc.).

I tried a formula similar to the book

=IF(HASONEVALUE(‘1097_Applicant_Data_Master'[Position Title]),[HolidayTarget_District],SUMX(‘1097_Applicant_Data_Master'[Position Title],[HolidayTarget_District]))

But get the error: Calculation error in measure ‘1097_Applicant_Data_Master'[HRSSC_ByDistrict_HolidayTarget]: The value for column ‘Position Title’ in table ‘1097_Applicant_Data_Master’ cannot be determined in the current context.

Everything I try is a bust !

Any suggestions ?

Thanks

Larry

PS a while ago you gave me the answer to try and use LinkBack to solve a tough problem… I did get it work after a bit of fiddling.

 

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

Sorry, please ignore last post… As soon as I hit the send button, I realized the error, as I was referencing the column not the table.  But even one repaired, the syntax, while works has the same result as not using the avergaex, it is still grabbing the “max” number for the roll up….

I think the whole statement has to be in a average(x), but not sure how that syntax would look?

Attachments:

You must be logged in to view attached files.

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

I tried that too, but got an column reference error, also tried it using “Values” as an expression after average.  So not thinking I do not have my syntax correct.  The one that works is the same, just without the ‘Average’ functions.

Do I need to do this as a Calculate?  If yes, how, I don’t need any filters, as we are performing that in the ‘if’ for the blanks.

As always.. thanks…

 

Attachments:

You must be logged in to view attached files.

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

THANK YOU !!!!!!

I am almost there ! Can I can beg a little more help ?

The formula works at the requisition level, however when I and the geographic elements to the row now (e.g. Area and District), the sub totals and totals, reflect the largest number (of course — because we said min and max !).

I tried to add an average, but it wants a ‘column’ reference.  I embedded the min max statements directly into the single formula, but that wasn’t helpful, as that just created a tad cleaner way of doing the same thing…

I tried to “cheat” and average the calculated field (but of course its the same thing)…

is there a way to use this math and get to an average?

Again, I cant thank you enough for the time you are investing is us far less skilled !

 

 

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

Here  you go, give this a shot, see if it helps…

thanks again for the help !

Attachments:

You must be logged in to view attached files.

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

Tom,

Again I appreciate all the help, and sorry for the delay.

After several discussion (ok arguments), I can’t gain permission to send the file, even in its obfuscated state, the opinion is that the headers are proprietary.  So I think I am stuck (other than describing basic type headers and relationships).

Again, thank you for the time you have taken to assist, it is very much appreciated.

 

[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 !

[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 !

[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 !

[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]

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.

 

 

 

[Author]

Posts