Separating Data Effectively

[ShadezExcel]

Good afternoon,

I have a model that I am trying to refine, including "breaking out" certain costs. Unfortunately, I do not have access to the actual tables at this time in order to make procedural changes that could automatically fix this.

When looking at the below, I would like to have the eventual pivot show the FRT COST transactions in a different column than the regular transactions. Similarly, I would like the FRT BILLED transactions to show up in another column on the eventual pivot than the regular product transactions. I am rather new to Power Pivot - basic measures are not difficult but from the books I have and searches I have done, the sorting in this manner can be difficult. Any ideas?

 

[scottsen]

Sorry, not super getting your question, or model.

I can maybe guess that rows w/ FRT-COST is not actually an "itemkey" ?

I don't think you are asking this, but hey... I'm not sure what else to answer... let's pretend you have a dollars column.

[Total Frt Cost] :=CALCULATE(SUM(Table1[Dollars]), Table1[itemkey] = "FRT-COST")

That would allow you to drop that measure separately and thus... have it on a different column. but i am skeptical that is what you are asking...

 

[ImkeF]

I'd consider handling this by an account-group-structure.
If you don't have a table where you're grouping your accounts already, just throw this litte tab into your data model:

AcctGroup1
FRT-COST
FRT- BILLED

and connect it with you itemkey-column.
This will create 3 groups for your accounts ("blank"-group for the non-matching No-accounts, but you could give it a nice name in your pivot table).

No need to do anything with your measures then.

 

[ShadezExcel]

Scott - thanks for the measure suggestion. The thing that I did not describe well was that this database is fed by multiple sources. When we incur a cost for freight that is not billed to the customer, a line is created in the table so that true profit can be eventually calculated. Your suggestion worked great for those items.

I guess on a larger scale, does Power Pivot have a function that could do something like CONTAINS or something? Having it pull out the ones I described above is actually (sad I didn't figure out how to do it myself) looking rather easy. I have other fields where I need to pull things out but where the ending numbers keep changing.

Imke - that is the future probably, but I am personally not there yet. I am trying to migrate away from something a consultant set up on the SQL side that I am not able to modify, PLUS the SQL programming is failing miserably to calculate profit margins and other needed measures. This is my first foray into something like this, and progress is exciting. If I was to make another tab, I would have to set up the relationship correct? This is a 250k line db that is completely flat at this point.

 

[ImkeF]

I think you found the perfect new home for your model

250k line is warmup-level for PP - actually here the performance will more depend on your columns (how much and how many distinct values in them - so only import the columns you actually need).

You'd create this table in your Excel workbook - load it to your data model and create the connection in the PowerPivotWindow
Have a look at these videos:
load table: https://www.youtube.com/watch?v=VxdAMsmSe70
create Connection: https://www.youtube.com/watch?v=jiJG5CJkj2M

I guess it's easier than you think - no need to postpone into the future

 

[ShadezExcel]

I have a decent amount of columns that are useless for what I am trying to achieve (with THIS project at least). I plan on trimming them before publishing/distributing my model. Collie's book talks about trimming quite a bit, and our network/server is slow anyways so I see the benefits for sure. On top of that, 32 bit

I will take a look at the videos! Thanks! I know the chapter on that confused the heck out of me, it reminded me of relational databases from a class that crossed programming and db stuff in college. Didn't do well then either with it! Practice makes perfect I guess.

 

[scottsen]

I guess on a larger scale, does Power Pivot have a function that could do something like CONTAINS or something?

Ya, it is called... umm... CONTAINS()

DAX Contains() Function| Power Pivot | Tiny Lizard

 

[ShadezExcel]

Ya, it is called... umm... CONTAINS()

DAX Contains() Function| Power Pivot | Tiny Lizard

I have read your blog post and looked at another topic somewhat high on the board where the guy was working with FEDEX and DELL. My knowledge of splitting/relationship tables is not good enough at this point to play around like in a regular spreadsheet. Still need to watch Imke's videos. I found workarounds for some things using basic formulas and ">=" with the first few letters of documents as the thing being measured against. This catches all of the documents after it. I do not mean to throw things together, but for now it is doing its job.