Trying to have a measure provide the same results as only the Grand Totals of the following vanilla PivotTable.
Table AcctList
Fields, [DueDate], [Amount], [Name], [Validated/Held]
Page Filters = [Validated] = “Validated”
Rows = [Name]
Columns= [DueDate]
Sigmas = Sum of [Amounts]
Row Filter = Top20 by Value Sum of [Amounts]
Column Filter = Date Filter = This Month
My Measure for the Totals
TotalValidated:= CALCULATE(sum([Amount]), AcctList [Validated/Held]="Validated")
The Following Fails as it looks at the Top20 per day and not the Total Amounts as filtered in the Pivot Table.
MyTop20:= CALCULATE([Validated Payables],TOPN(20, AcctList,[Validated Payables]))
I plan on also providing the BotomN amounts by simply subtractacting the MyTop20 from TotalValidated
Table AcctList
Fields, [DueDate], [Amount], [Name], [Validated/Held]
Page Filters = [Validated] = “Validated”
Rows = [Name]
Columns= [DueDate]
Sigmas = Sum of [Amounts]
Row Filter = Top20 by Value Sum of [Amounts]
Column Filter = Date Filter = This Month
My Measure for the Totals
TotalValidated:= CALCULATE(sum([Amount]), AcctList [Validated/Held]="Validated")
The Following Fails as it looks at the Top20 per day and not the Total Amounts as filtered in the Pivot Table.
MyTop20:= CALCULATE([Validated Payables],TOPN(20, AcctList,[Validated Payables]))
I plan on also providing the BotomN amounts by simply subtractacting the MyTop20 from TotalValidated