Forum Replies Created
-
Posts
-
Steve,
For the first measure, other than product “style” and a calendar period, what does your first pivot have on rows, columns, report filter, and slicers?
For Power Pivot, one of the first things I try in a case similar to yours, is to create a “custom” or dynamic table that would use a measure like [FIRST SENT TO STORES AGED UNFILTERED] is an added column (ADDCOLUMNS) and then use the dynamic table as an input for a measure like [SELL THROUGH TREND].
If you could produce some sample data (fictitious data that uses a real model), I might be able to be more specific.
When you look at the tables in diagram view, they are missing the relationship between ID columns.
Once the relationship was added, column was populated.
Please see the pivot on worksheet “Desired Result (Grouping)” in the attached workbook
Would a blank be suitable for a “missing” label or would you want a phrase like “missing exam”?
ID 1 and ID 4 have the same sequence (Eng, PTD, Mat), but different groups, why?
In your workbook there are 3 exams, but your example only gives the order of 3 sequences. What about PTP, Mat, Eng; Mat, Eng, PTP; Mat, PTD, Eng.
Would it be impossible to have taken any one of the exams more than once? If so, how is that enforced?
Would it be possible to have taken less than three exams? If so, what then?
What version of Excel are you using?
Tom
Steven,
Regarding SUMX, times change and the code behind the functions improves. What was thought in years gone by (e.g., 2014) does not always apply for today. Current thinking on SUMX is that in some cases it is faster than SUM. You can read more about SUM and SUMX here.
If you have a valid calendar (dates) table in your model, then you could try
Sales Trans Count Last Year := CALCULATE ( [Sales Trans Count], SAMEPERIODLASTYEAR ( dCalendar[Dates] ) )Consider using SUMMARIZE before you determine whether 1 or -1. A formula like the following should work better:
Sales Trans Count := SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'SALES TABLE', 'SALES TABLE'[TRANS#], "NetUnitsSold", SUM ( 'SALES TABLE'[UNITS SOLD] ) ), "SalesTranCount", IF ( [NetUnitsSold] > 0, 1, IF ( [NetUnitsSold] < 0, -1 ) ) ), [SalesTranCount] )Strange, isn’t it?
In your pivot table, when you choose to sort using a measure column, you are actually sorting *one* of the row label columns by that measure, not all of the row label columns.
An advanced topic (please see attached workbook): instead of creating a pivot, to get your desired result where all columns are sorted by a measure, you could embed a DAX query into a worksheet, with a formula like the one at the end of this post (begins with EVALUATE and formula returns a table, not a scalar value).
To see the formula in the Model Query tab of the workbook, right-click inside the table and choose Table from the menu, then choose Edit DAX… from the drop-down list.
EVALUATE ADDCOLUMNS ( SUMMARIZE ( Data, 'Bill Tos'[Bill To Name], Shippers[Shipper Name], Consignees[Consignee Name], Commodities[Commodity Name] ), "Order Count", [Order Count] ) ORDER BY [Order Count]Garrett,
In the Power Pivot window, on the Home tab, choose the drop-down arrow beneath the PivotTable button and choose “Flattened Pivot Table” and, when prompted, choose whether you want the pivot to appear on a new worksheet or not.
Once the pivot is created…
First, put your Order Count measure into the pivot’s Values drop zone.
Next, in the Rows dropzone, drop or drag the fields for BillTo_Name, Shipper_Name, Consignee_Name, and Commodity_Name.
If you do not have exactly what you asked for, let me know.:)
Technically you could have entered your name fields into the Rows dropzone first, and then added your measure into the Values drop-zone, but without adding the measure first to the pivot, you will see all possible combinations of the various names along the pivot rows. Sometimes seeing all possible combinations of names along the pivot’s rows makes a new user feel something is broken.
Tom
Steven,
Scott Senkeresty, who also works at PowerPivotPro has commented elsewhere on Fast Load:
https://www.mrexcel.com/forum/power-bi/893640-fast-data-load-power-query.html
I think a general strategy for slow Power Queries is to push as much pre-processing off onto the source database.
Tom
Excel 2016 allows for variables, which often allows formulas to execute faster.
Try this:
Sales for Days 91 Thru 180 := CALCULATE ( SUM ( 'Order Detail'[Sales] ), FILTER ( 'Order Detail', VAR vDaysSinceHire = 1.0 * ( RELATED ( 'Order Header'[Invoice Date] ) - RELATED ( 'Sales Rep Data'[Hire Date] ) ) RETURN AND ( vDaysSinceHire >= 91, vDaysSinceHire <= 180 ) ) )The line that starts with VAR and ends with RETURN is the definition of the variable.
The line with AND could have been written instead (without the AND) as
vDaysSinceHire >= 91 && vDaysSinceHire <= 180The advantage here of using a variable is the days since hire would be calculated once instead of twice for every row in the order detail table.
In this case, are you using Excel 2013, 2016 or Power BI Desktop?
John,
There are many ways to create filters in DAX, but the means you have chosen (a “top level filter” inside CALCULATE) is meant only to filter a single column. It has a lot to do with the data model is based on a columnar (not a row-based) database.
You mention that [Sales] is a column, but you use it like it is a measure (if a column and not a measure, you need to tell the formula engine how to aggregate the data, for example SUM ( ‘Order Detail'[Sales] ).
If you are using Power Pivot (and not Power BI Desktop with bi-directional filtering set) you might also have a problem with a calculation using columns from two tables that are probably not directly related to each other.
Without seeing your model, and also assuming that [Sales] is a column in the ‘Order Detail’ table (not a measure), you will probably get better results with a measure like:
First 90 Day Sales = CALCULATE ( SUM ( 'Order Detail'[Sales] ), FILTER ( 'Order Detail', RELATED ( 'Order Header'[Invoice Date] ) - RELATED ( 'Sales Rep Data'[Hire Date] ) < 91 ) )Brian,
Excel (powerpivot) is an interesting case: when building a data model, power pivot exposes the model as tabular, but under the covers it is a multi-dimensional (OLAP/MDX) model. Because of this, you can write cube formulas in an Excel worksheet that query the Power Pivot data model.
This is different in Power BI, where the data model is foundationally a tabular model.
Mike,
If you want to calculate compound interest for individual entities over years, and then add the compound interest for each group, please try the following and let me know if I have misunderstood what you want:
Test 2 := SUMX ( ADDCOLUMNS ( CALCULATETABLE ( 'Group' ), "CompoundInterest", VAR vMaxYear = MAX ( 'Year'[Year] ) VAR vGroup = 'Group'[Group] VAR vEntity = 'Group'[Entity] RETURN PRODUCTX ( FILTER ( ALL ( Test ), RELATED ( 'Group'[Group] ) = vGroup && RELATED ( 'Group'[Entity] ) = vEntity && RELATED ( 'Year'[Year] ) <= vMaxYear ), 1.0 + [Interest] ) - 1.0 ), [CompoundInterest] )
