Sum of TOPN by Filtered Date Range

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
I’m not totally clear what filters you are putting on your measure, but try trapping the TOPN in a CALCULATETABLE with ALLSELECTED ( AccList[DueDate] ) as the second clause.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
I guess what I am not doing is feeding the TOPN the right Table. I should (?) be feeding the TOPN a summary table of the Amounts by [Name] with the recommendation of the ALLSELECTED by gazpage.

I think I still have some "old learning" in my way of the new. In the meantime, I will be iterating on Chapters 4 and 5 of Marco's book, with Matt's and Rob's books too.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,291
Messages
5,485,975
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top