There is also the Summary card, also not customizable, but may offer the aggregations you are looking for:
Summary card works on individual worksheet but not a dashboard. Here is a better description of what I'm trying to accomplish. I created a worksheet that I use as filter Actions on a Dashboard. I would like to be able to see the average of selected points on a graph. I may need to create a formula to accomplish the task. When I select the points on the graph it gives me the Sum of the points selected. The graph consists of thousands of records with 11 regions assigned to them over a 16 month period.
I want to know what the average revenue was for last year and what the average is YTD. If I select points on the worksheet where the Summary Card is displayed I can see the average changes as I select different points on the graph. Is there any way to accomplish this task using filter Actions?
I've attached a file for review - any help is appreciated.
pccidashboard_rev1.twbx 2.3 MB
Here is my first pass on your request. I have setup two action filters and an additional data connection that is a cross-join with your data and every month, so if you have 10,000 records and 16 months, this cross-join data set will have 160,000 records. You could modify the custom SQL to make it more dynamic with a sub-query.
I was not sure what was going on with your current summary card, I could not get it to work as expected, so I remade it using a WINDOW_ functions, so you get the sum and average of the marks selected. Since each mark is an aggregate, a sum of the records for that month, this new summary sheet is an aggregate of an aggregate, either a sum of a sum or an average of a sum.
The secondary summary card will only populate when a single mark is selected, and provide a YTD average of sum and a Prev year average of sum for each month. This sheet uses the cross-joined data set because an action filter happens before table calcs are evaluated, and for what you requested, the table calcs need access to the unfiltered data. In a way the cross-join turns the single select action filter into more of a single select parameter.
There are many more things that can be done with this structure, you can get year-over-year values, or YoY YTD, or just about any calculation you want.
Thanks for providing a solution to my problem. I'm not very familiar with SQL so I have a question as to the other SQL that is referenced in the sample 'twbx' file. After I unpackaged the files I saw there was an additional file that is called 'equip data.xls'; will I have to update this file each time I update my data?
I figured it out, again thanks for your help.
I'm stuck. Some of the formulas are not working in my workbook. It was suggested that I use the Window_Avg to see the average when I select one or more points on a graph. The Window_Sum works but some reason the average doesn't work. I've attached the SQL code that I'm using: SELECT ['Month Filter$'].[Fiscal Month Filter] AS [Fiscal Month Filter],
[Data$].[BU] AS [BU],
[Data$].[CCT] AS [CCT],
[Data$].[Equip $] AS [Equip $],
[Data$].[Fiscal Month] AS [Fiscal Month],
[Data$].[OMS] AS [OMS],
[Data$].[Proj Mgr] AS [Proj Mgr],
[Data$].[Region] AS [Region],
[Data$].[Test Date] AS [Test Date],
[Data$].[Type] AS [Type]
FROM ['Month Filter$'], [Data$]
The sql code should have joined Fiscal Month field from both of the data sources that is referenced in the code, but it did not. Thanks for any help.
I am sorry, but I think there is some confusion. Attached is the workbook that I edited earlier (old attachments are currently missing)
Does the attached workbook contain all the calculations you want? and do they work correctly?
The cross join data set was for replicating the benefit of a parameter so a filter action can have the benefit of a parameter, and year-over-year YTD numbers. the other functions are on your data filtered with the action filter.
Can you provide additional details on what you are dealing with and what you want for a result, and how it is different from the attached workbook?
Your attachment is exactly what I'm trying to accomplish, however I was not be able to get it to work completely. The Window Actual Equip $ Avg on the PCCIRevenueSummary worksheet formula isn't work and the formulas on the Prev Year and YTD are not working either. I think I must have missed a step that is in your workbook. I have not been able to figure it out. I'm not able to attach my workbook due to maintenance on the Forums.
So you are trying to recreate the calculations? I sounds like a compute using setup difference. I would be glad to help if you can provide a packaged workbook that represents what you have recreated so far.
I do not believe there is any restriction on posting attachments on the forum currently. The current issue only effect old attachments because of a renumbering of post IDs.
There are a number of differences between the method I used, and how your attached workbook is setup:
on the worksheet "Prev Year and YTD":
1. change the Data Type for both "Fiscal Month" and "Fiscal Month Filter" to Date instead of Date Time
2. place them both on the Level of Detail shelf as Discrete and All Values.
3. for both green pills on the Measure Values shelf, bring up their right-click context menus and from the Compute using sub-menu, select "Fiscal Month"
on the dashboard "PCCI Dashboard":
1. from the main menu, select Edit->Actions...
2. select the action named "Single Select" and Edit
3. enable the option for "Run on single select only"
4. set the "Clearing the selection will" option to "Exclude all values"
5. under "Target Filters" select "Selected Fields" and click the button "Add Filter..."
6. for the Source Field, select "Fiscal Month"
7. for the Target Data Source, select "Custom SQL(Month cross join)"
8. for the Target Field, select "Fiscal Month Filter"
9. OK all the dialog boxes.
you should not have what I have in the attached after selecting a mark on the dashboard.
Joe, the Window_Avg formula isn't working on the PCCIRevenueSummary worksheet. It wasn't working in the workbook that I uploaded earlier. If you can help me with fixing that then I'm good. I am getting a sum of the Actual Equip $ instead of an Average. Here's the formula.
IF FIRST()==0 THEN
WINDOW_AVG(SUM([Actual Equip $ Sum]),0,IIF(FIRST()==0,LAST(),0))
Again thanks for all your help.
what do you want to average? The values of the marks displayed? or all values in the data source? or some other level?
If I select multiple points on the Forecast Accuracy worksheet I want to know what is the average of the points selected. For instance, selecting Jan - Mar - what is the average revenue for those months?