[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Hi Tom,

Sorry, my question could have been clearer… See attached workbook.. Our customer table connects to our sales file based on Cust#, but our customers may have a few diff Cust#s.

When we go to use this rankx (topn) style formula, I want to tell the formula to rank customers based on ‘group name’ rather than over all the individual customer numbers…

Our work around is to related group name to our sales file from the full customer table… then bring in a second trimmed version of the customer table that only lists the unique customer group names… then point the rankx formula to rank off of just the group name table…

Unless there is another way to do this in the measure..

Thanks for looking!

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

thanks Tom.. for me it goes in waves.  lately more than ive seen in a long time.  at times i cant connect to any file and have to reboot!  power problems lol.  thanks for the feedback 🙂

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

We figured this out.  Thanks,

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Okay thanks Tom.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks for sharing that outline Tom.  I hope to take this course before year end.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

That’s awesome!  Thanks for letting me know.  I really do enjoy the site, and the teaching approach works well for me.

I am an evangelist for sure 🙂

Tom, can you tell me if there is a outline for the online $349 course the site offers?

Keep up the great work!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

I simply didn’t know the correct syntax.  Thanks again!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

That is very helpful..  Somehow I missed the point that these were used in intermediate tables.  This makes more sense now.. Thank you!

The link below led me to believe that I could simply replicate a calculated column in a measure/calc field.

http://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

The first example is what led me to believe I could simply use a measure to calculate the YEAR from the date column, then use that new column in my Pivot.

Thanks for explaining this to me.. sometimes I feel a little DAX dumb 🙂

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Okay, I am trying to add a column in a measure to bring back the ‘year’ in the attached example.

I get an error referring to multiple columns?  I got this example from SQLBI and am not sure why this is not working.

Thanks,

 

 

 

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Great points.  thanks again for taking the time. branching out to dax as an excel user, this site and approach works best for me.

 

ill be back 🙂

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom,

I ended up being able to create a measure that un-dos the filter in the pivot, allowing for a comparison of the current store’s qty sold vs. another (CALC ALL) column showing the least selling store regardless of current store in the filter.  Thanks for the help on all of this.

Also took your advice and checked out ALLEXCEPT on the blog posts.  I read the three part series that compares ALL vs. ALLEXCEPT.  Also read up again on IF VALUES.  This stuff isn’t natural to me (like Excel formulas) quite yet, but I am definitely seeing the similarities when coming up with logic formulas.

Thanks again!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks so much Tom for the workbook you sent.  I have what I hope is just a quick follow up…

I thought I would be able to create a pivot that would show me Store# and Qty sold AND also include the ‘LeastStore’ measure in the same pivot report.

I put an ALL(store) filter within the LeastStore Calculate, but it does not seem to look past the current location being filtered in the pivot?

The file attached shows the new pivot and what I’m trying to report.  For example, Product A should return store 3 as the Least, regardless of current store filtered?

I am used to using ALL in my Calculates, is there a different way to accomplish this?

Many thanks for taking a look at this,

Mark

 

 

Attachments:

You must be logged in to view attached files.

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Thanks Tom for all those measures; they will keep me busy for a while.  I re-created all of them in my model and will let them sink in 🙂

Thanks for commenting on Query and BI.  I just tried Query the other day in 13 and definitely see the value in using it regularly.

 

Thanks again!!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom,

One more quick question.  If there is a TOPN.. is there also a BOTTOM type function?  We also would like to return the ‘least’ selling store.

Thanks again!

[Mark WalterParticipant Started: 9Replies: 19Total: 28]

Tom you rock.  That’s brilliant.  Not everyday you see a measure that returns text you can use in the value box 🙂

Are you doing much with Power Query? BI.COM?

 

Thanks again!

 

 

[Author]

Posts