I have a Power Pivot table called "tblVelocity"...
- There is a column called "Team" (and there are many teams in the underlying Excel table)
- Each row of the Power Pivot table represents team performance during the previous scrum sprint as measured in a column called "Ratio". It looks like this:
- ROW 1 = [Team] = "Yankees"; [Sprint] = "Sprint-1"; [Ratio] = 95%
- ROW 2 = [Team] = "Mets"; [Sprint] = "Sprint-1"; [Ratio] = 105%
- ROW 3 = [Team] = "Giants"; [Sprint] = "Sprint-1"; [Ratio] = 100%
- ROW 4 = [Team] = "Yankees"; [Sprint] = "Sprint-2"; [Ratio] = 87%
- ROW 5 = [Team] = "Mets"; [Sprint] = "Sprint-2"; [Ratio] = 102%
- ROW 6 = [Team] = "Giants"; [Sprint] = "Sprint-2"; [Ratio] = 96%
As you can see, there are 3 teams, each with 2 [Sprint] and [Ratio] values. I use this Power Pivot table to inform a pivot table in Excel. I then use that pivot table to inform a standard (non-pivot) chart in Excel. The chart visualizes team performance over time, and it works as expected.
What I am struggling to do is find a way to modify the value of "ratio" depending on the unique team count after a filter is applied in the Excel pivot table. For example, imagine I click the [Team] slicer on the Excel table and multi-select "Yankees" and "Mets". What I want to do is force all the [Ratio] values to zero so that the chart effectively does not render anything meaningful. I only want to render the chart if the filter returns exactly one team.
What I think I need to do is create a formula-based "Adjusted Ratio" field in the Power Pivot table and set its value to:
- IF (Visible_Unique_Team_Count = 1, [Ratio], 0 * [Ratio])
Then I can base my chart on the [Adjusted Ratio] (instead of [Ratio]), and when it's equal to 0 the chart won't have any data to show.
The problem is that I can't figure out how to calculate "visible unique team count." I have tried various versions of this:
Visible_Unique_Team_Count :=
SUMX ( VALUES ( tblVelocity[Team]),
CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]=[@[Team]]))
I have seen only one of two results. Either the formula I use results in an error or it results in the total count of teams regardless of whether the filter is applied (and so the ratio is never adjusted).
Thanks
Hi Shawn,
You cannot use dynamic team name to filter the CALCULATE function:
CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]=[@[Team]]))
CALCULATE accepts values like:
CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]="Team Name"))
For dynamic values, you have to use the FILTER function:
CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), FILTER(tblVelocity[Team], tblVelocity[Team]=[@[Team]]) )
If you want it to react based on pivot slicer context, use ALLSELECTED() function in CALCULATE filter.
Thank you, Catalin. I appreciate your quick and thorough response. Unfortunately, that is one of the ways I previously attempted to do this. However, the following formula causes the error below.
Please notice:
- The table (tblVelocity) and team name ([Team]) values within the FILTER function have been underlined in red by Power Pivot
- Power Pivot also added additional parentheses to the end of the formula
- The actual name of the field I use is "Team Count Helper", not "Visible_Unique_Team_Count"
Team Count Helper := CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), FILTER(tblVelocity[Team], tblVelocity[Team]=[@[Team]]) )
============================
Error Message:
============================The end of the input was reached.
The calculated column 'tblVelocity[Team Count Helper]' contains a syntax error. Provide a valid formula.============================
Call Stack:
============================at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj, ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options)
at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateEntryCollection.DoUpdate()
at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateOptimizer.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.SandboxTransactionAmo.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.Update()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.UpdateAddedColumns(List`1 addedColumns, List`1 oldExpressions, IList`1 colIndices)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean suspendImpact)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions)
at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)============================
In Power Query/Power Pivot, there is no row context, calculations are made on entire columns.
Therefore, @ cannot be used as you do in excel tables to indicate current row. Just refer to column names in formulas added to calculated columns.