Forum Replies Created
-
Posts
-
Hello Larry,
Attached is a sample Excel 2013 workbook, showing a listing as you described. Note that column C is hidden.
Hello Larry,
I think I know the issue, you have created a pivot table and dropped Requisition # and Application # on the rows drop zone, and (while Excel/PowerPivot is waiting for you to select a field for the values drop zone) it is listing all application #’s under each requisition #. But you don’t want to calculate anything…
Let me know if I have this right, and if so, I can post a sample workbook. Do you have Excel 2010 or 2013?
Hello Gaetan,
In case you were wondering how I created the disconnected tables for Report Year, Start Month and Start Year, the SQL statements follow (I created a table in concept similar to your Periods table):
For Report Year:
select distinct [Année Civile] from Périodes order by [Année Civile]
For Start Month and End Month, the SQL statement is the same:
select distinct Mois from Périodes order by Mois
Hello,
I am pleased you are asking questions, that is how I also learned my answers.
Regarding Question #1:
All measures must be aggregations.
If you are “creating a measure” in the values drop zone of the pivot field list, Excel is providing an aggregation for you, which by default is a sum for a numeric column and count for other data types. This type of measure in Excel is sometimes referred to as an “implicit measure” because Excel creates it for you (when you look at the column name in the Values drop zone, you will see Excel has changed the name to something like Sum Of… or Count of …
If you are creating a measure in the Power Pivot window you are responsible for specifying the aggregation. If you do not specify an aggregation for a measure inside of Power Pivot, you will get the message “column … in table … cannot be determined in the current context”. Because you must specify the aggregation inside of Power Pivot, this type of measure is often called an Explicit measure.
Regarding Question #2:
If I understand this question, you are asking why in a Power Pivot pivot table (e.g., on the Excel side), the pivot table takes on an outline form, where in the Power Pivot window a table does not appear in an outine form.
The answer to this question, is that in Excel you can make your “Power Pivot” pivot table appear in a tabular or in an outline format. For pivot table rows you can change between outline and tabular form by right clicking on the little arrow just to the right of a field name in the Rows drop zone. When the context menu appears, choose Field Settings. In the dialog box that then appears, choose the Layout and Print tab. There you will see options for showing items in outline form or tabular form; choose as appropriate. Also important is a checkbox for “Repeat Item Totals”.
Gaetan:
I searched a copy of the workbook sent to you for a reference that would include:
LC (-3
but could find nothing. It does not look familiar at all.Can you tell me what cell has the formula?
I am going to send you a revision of the dashboard. When I first checked for references to cells B8 and B9, I only found them in cells D13 to D22, so those were the only calculations I worked with.
I noticed may other formulas further down in column D that had a used a value of:
#REF!
in the formula and used the original measure. The new revision updates formulas in the other cells found later in column D with the modified measure and references to the slicers.If the new measure does not apply to cells below D22, feel free to discard the Revision 02 that I will send you via email now.
Your questions are good, I am happy to answer them.
Regarding && you are correct, it means AND. I use it instead of AND not only because it is shorter, but because the AND function is limited to 2 conditions and the && is not. Technically AND is a function and && is an operator.
TOPN can be used for text like MIN and MAX are used for numbers. MINA and MAXA, meant for text, did not give me the results TOPN is different from MIN and MAX because MIN and MAX always return single values, but TOPN returns a table. The 1 (the N value) at the beginning of TOPN means I only want one row in my result (which will be either the “top 1” or the “bottom 1”. The last parameter, 1 or 0, refers to the sort order, 0 is for descending order, 1 is for ascending order.
Hello Gaetan,
I am glad you received my email with attached dashboard. I am pleased also you noticed how the slicers (segments) worked with disconnected tables to manipulate critical cells in the workbook.
The next step will be to evaluate how to create a new measure that responds, within the Power Pivot window, to the slicers for the selected date range.
Hello Gaetan,
I sent you an email with an attached modified dashboard.
If you have difficulty with capturing the amounts for the range of months, the next step would be to create a new measure designed to responds to actual start and end dates. This would mean the date range logic would be built into the measure in Power Pivot, not in the cube formulas (your cube formulas will be simpler as a result).
Let me know if you have any questions on the changes I made, and when you are ready to take the next step.
Julie,
A linkback table is the result set in Excel from a query of Power Pivot tables.
Here is a link you can follow to learn more:
http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/
When you read the linked reference on linkback tables, remember you can ask questions here on these forums and your thoughts will also help others learn.
Hello Gaetan Garin,
I am trying to express your question in US English, please tell me if I my understanding is correct:
You are working with two tables, “Periods” (a calendar table) and “Seen Writings with Account Reporting” (a data table).
These two tables shared a relationship with their date columns.
The Periods table has a least 3 columns, [Date], [Month] and [Calendar Year].
The “Seen Writings with Account Reporting” has at least two columns, which I will also call [Date] and [Signed Amount]. This table also has a measure (calculated field), which is the [Sum of Signed Amount].
What is think you want is for a user to select a date range in Excel and Power Pivot will return the [Sum of Signed Amount]. If this is true, the solution I will propose is a set of two slicers (I think you call a slicer “segment”). The slicers will contain calendar dates, one date to “Start” and the other date to “End”. First, these slicers will be integrated with a pivot table, then I will help you provide a solution using a cube formula (ValeurCube).
When you let me know my understanding is correct, I will proceed with the solution. From how you expressed your cube formula, you are using Excel 2013. I am planning to submit a copy of my workbook as a demo; hopefully it will function correctly in your work environment.
Hello JulieSam,
Because you “have to generate rank like this in power pivot”, you would also need to use a column that has criteria (not currently shown) to rank the country-state combinations.
In order to demonstrate a way how to achieve this, I modified your data by making the rows more random, and adding a “criteria” column (the name of the column is not important, practically any column of numeric data would do):
Country State Criteria
India Kerala 1.1
US Florida 1.1
India Orissa 1.1
India Tamil Nadu 1.1
US NewYork 1.1
India Kerala 2.1
US Florida 2.1
India Orissa 2.1
India Tamil Nadu 2.1
India Kerala 3.1After importing the data into Power Pivot (and naming the sheet-tab MyTable), I created a calculated column [Country State]:
= [Country] & ” – ” & [State]
Then I created another calculated column [Rank]:
=
CALCULATE (
COUNTROWS ( MyTable ),
FILTER (
ALL ( MyTable ),
MyTable[Criteria] <= EARLIER ( MyTable[Criteria] )
&& MyTable[Country State] = EARLIER ( MyTable[Country State] )
)
)Returning to Excel from the Power Pivot window, I created a pivot and put Country, State and Rank columns on the Rows drop zone. The rest was just formatting field settings and pivot table options.
I also created a linkback table in Excel, using this formula:
EVALUATE
SUMMARIZE ( MyTable, MyTable[Country], MyTable[State], MyTable[Rank] )
ORDER BY MyTable[Country], MyTable[State], MyTable[Rank]Attached is a copy of the workbook in 2013 format. The formula for the linkback table can also be found by right-clicking the table on the LinkBack tab, choosing the Table option, then Edit DAX.
If you have Excel 2010, let me know and I will also post a workbook in 2010 format (less the linkback table, which cannot be produced in Excel 2010).
If you have any questions, just reply to this email and I will answer them.
There are solutions available, which could be as simple as a single DAX function, but may require a multi-function combined into a single formula. Before attempting an answer, let me first ask:
Have you already started a data model in Power Pivot. If not, the first task would be looking at formulating a basic model in Power Pivot, including a “Calendar” table.
Are you using a standard calendar (Jan 1 – Dec 31), a fiscal calendar (Apr – Mar or Jul – Jun or Oct – Sep), or a custom calendar like a 4-4-5?
For this cumulative sum are you trying to cumulate over month-to-date, quarter-to-date, year-to-date or a custom period-to-date or a lifetime-to-date?
Is your source data coming from CSV files or a relational database like SQL Server?
I am familiar with GL reports in accounting. It would help though to know about your tables and relationships in your current Power Pivot model.
