[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi Tom, Sorry for not being clear.

Yes I’m talking about the window that comes up when you click MANAGE.

My tables wont stay in the order i put them in. Then within the tables my measures from time to time clump all in the first few columns of the table.

 

Steven

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Ok, so I’m not alone with this.

To be clear, I’ve created a dummy table in excel. Brought it into powerpivot and then under the columns created the measures. That have just moved to the first 3 columns in the pivot table. Rather than being neatly placed under many columns.

 

Another question I have is when I move pivot tables to order them. When I reopen the file they are back in the same order. Which i think is the order they were added to power pivot. Am I correct in assuming that?

 

Thanks

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi Tom, Thanks for responding.

In power pivot i have dummy columns created. Under the measures section I’ve created 150 or so measures. They were sorted under each column IE This Year, Last Year, 2 Years Ago. And the associated measures were under each column. Then when I opened Excel today I see everything is under the first few columns. Not nested under the 10 or so columns created.

 

I would love to find a way to resort them.

Not have to cut and paste the measures under the correct column.

 

Very strange.

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I have some sample data.

Is there a way for me to upload it privately or send you a dropbox link.

Thanks.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks for the info. I will read up on the great reading.

Question since you seem to know a lot.

I would like to write a POWER QUERY statement and tell it to trim not the left 4 characters. But if it sees “$” use the RIGHT 3 of that, or if it sees “PC” use the 2 to the left.

This is what i currently use:

= Sql.Database(“SERVER”, “DB0001_160517195223”, [Query=”SELECT LEFT([disc_code], 4) AS [POS DISCOUNT AMOUNT]#(lf)FROM tbldiscount#(lf)GROUP BY LEFT([disc_code], 4); “])

Thanks

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I’ve been applying the suggestion given above to a few situations. But now I have a curve ball so to speak.

I would like to know the last discount given to an item. But the code above is looking for things based on a sorting logic.

But see below my issue, see attached image

This is the calculated column formula.

=
CALCULATE (
LASTNONBLANK (tblInvPriceDisc[disc_code], 1 ),
TOPN (
1,
FILTER (
tblInvSkuMaster,
tblInvSkuMaster[SKU]
= EARLIER (tblInvSkuMaster[SKU] )
),
LASTDATE ( tblInvPriceDisc[start_active_dt] )
)
)

The correct amount should be ($20N010919), but this is what is chosen ($30N071818)

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Wow, that is great. Thank you so much.

I can see the application of using that in other areas.

You have helped me a ton.

 

Cheers

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, thanks for your help.

Last question I have is now I want to be able to subtotal by department. But since it’s a measure I can’t and can’t create a slicer from it either.

 

What are your thoughts?

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks, I think that did it.

 

I did try every way I could think of, was close but just couldn’t finish.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks so much for your help.

 

I looked everywhere, but just couldn’t find anything.

 

The sample data was great. I followed it creating a calendar table and now can select the dates.

 

Yet again you are a lifesaver. I really can’t thank you enough for all your help on the board/forum.

 

Steven

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi Tom, thanks for the reply.

So your suggestion is to use either Dax or M?

Is one better than the other?

I’m trying to follow best practices.  For creating new files.  I need the most speed as our files are 100mb and can take 15-20 minutes to poll our local sql server (retail system)

 

thanks

[sjhc1177Participant Started: 25Replies: 28Total: 53]

I’ve attached a screenshot of the table.

Attachments:

You must be logged in to view attached files.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks for the post.

That did the trick.

Can you explain the FIRSTNONBLANK LINE?

I have a pitvottable I created with the sales results. I have some transactions where an item was exchanged. So the value of the net transaction is zero. But it still counts as a transaction and therefore the sales associate is in my list. I would like to filter on transactions <>0.

But you can’t filter measures. IE a months worth of sales for you equal 0 at location 1. So I don’t need you listed. But in location 2 you have sales of $100. So you should be listed.

How to I get the list to only list non zero NET transactions.

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, additional thought.

It would be even better to see all the pay rates for each employee Ranked.

To see each date we provided you an increase.

 

thanks

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Not sure if this helps display the issue.

Attachments:

You must be logged in to view attached files.

[Author]

Posts