Forum Replies Created
-
Posts
-
When I work on a project with many measures, I use “dummy” tables with appropriate names that are each associated with a specific category of measures.
Not sure I understand the other question. Are you saying that a pivot table on an Excel worksheet that you have moved to a different location on the same sheet (or to an altogether different sheet) reverts back to its original location once you have reopened the workbook? Or are you describing something in the Power Pivot* window?
_* I use the term Power Pivot window to mean what you see after you click the “Manage” button of the Data Model group in the Power Pivot ribbon.
Steven,
That is interesting.
When it comes to ordering, sometimes the grid area below a table where measures can be entered has a mind of its own.
Instead of creating dummy columns, you might consider creating dummy tables and move measures as appropriate. It is a relatively common practice (not everyone follows it, but quite a few do). One person I know starts the dummy table names with an underscore (e.g., _MyMeasureTableName), allowing the measure tables to group together in a pivot’s field list.
If you are talking about measure order in the pivot table field list, have you tried selecting the option to Sort A to Z under the field list settings icon (near upper right corner of Pivot Table Fields dialog on my machine, probably the same location on yours)?
Again, if you are talking about measure order in the pivot table fields list, what some people do is create tables with no rows just to group like measures. For example, there might be a table in the Power Pivot window called ‘Product Measures’, another table called ‘Customer Measures’, another table called ‘Sales Measures’, and so on. After creating these tables, they just use the Manage Measures dialog to change the table a measure is associated with (see the Measures dropdown in the Calculations group on the Power Pivot ribbon). Combined with the option to sort measures A to Z, you should get something quite workable for you.
You may need to re-think your measures to generalize how they handle the brands that change from month-to-month.
Difficult to say more unless you can post a small workbook sample that shows how the values change from month to month.
GetInfo looks like this:
Get Info := IF ( HASONEVALUE ( DirectoryInfo[Branch] ) && HASONEVALUE ( DirectoryInfo[Key] ), VALUES ( DirectoryInfo[Value] ), "Multiple Info Rows" )Anita: The source file(s) should be available as attachments in their respective original posts.
Porter444: If you still subscribe to P3 forums, just wanted to give a shout-out to you and say it’s been a while…
Are you using RankingContext as a bridge table to handle a many-to-many relationship?
Hi,
Most calendar table implementations that I have seen create the table in Power Query.
Rob Collie and Avi Singh created a clever Power Query for a calendar table that created start and end dates from a data table (see chapter 20 of Power Pivot and Power BI).
Ken Puls created an interesting implementation for a calendar table that uses parameters created in Excel (see chapter 24 of M is for Data Monkey).
Since you are using SQL Server as the data source, I would encourage you to consider using that resource to create the calendar table for you. I wrote a post a while back for how to do that.
Tom
Please see attached workbook for a solution.
What I am trying to say is that if you have a choice where to make a calculated column, it is most often better to create it before importing into the Excel data model (Power Pivot window).
Since your ultimate data source is a SQL Server, it would be considered a best practice to create the calculated column in a database view, before it ever reaches Power Query (M).
If you are not allowed to make the change in the actual SQL database, Power Query would be the next recommended step.
When you make a calculated column in the Power Pivot window, you are likely to loose the benefit of some data optimization provided by the DAX engine that is applied during import. Calculated columns are evaluated after that initial optimization occurs.
How many rows are in your 100 MB file(s)?
How many columns are in your fact table? Do you need all of them?
If you are creating a calculated column on imported data, it is almost always better to create the calculated column before reaching the Excel data model (Power Pivot window), whether it be in M query or in the original data source.
If your pivot excludes the entity, how should the pivot select the entity on which to calculate the profit margin?
Please see attached workbook that creates an orders lookup table, plus a fact table for campaign sales and a separate fact table for cross sales.
The calculation for cross sales per part description uses many-to-many syntax, which you can read about here.
Peter,
The following measure is listed:
SumofEffort=sum(task[name])
Should it be listed as:
SumofEffort=sum(task[effort]) ?
Since task efforts contains a percentage of a resource, I assume that the relationship is that one resource has many tasks, which also implies that the resources are booking groups. Am I correct in these assumptions?
It is also mentioned that booking group is used for the pivot’s column headers, but is that the same booking group column used in the relationship? If so, are you using the booking group column from the lookup table or from the detail table? I am assuming that you are using the booking group column from the parent table (Resources, based on previous paragraph).
If all of my assumptions are correct, your measure that combines resources and tasks should look more like:
SUMX ( Tasks, Tasks[Effort] * RELATED ( Resource[Hours] ) )
Please see attached.
Fill down is a cool feature in Power Query.
