cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danielrsnyder
Frequent Visitor

Top N by gift amount with fiscal year filter

I have created a report to look at our fundriasing by fiscal quarter over the past 5 years and also a table to show the top 5 gifts. I wanted users to be able to filter a few different ways so I added the filters in the upper right corner. The fiscal quarter and purpose filters work fine (independently and together), but when you filter on a full fiscal year, gifts are missing from the Top 5 table? Any ideas why that might be happening?

 

Fiscal Quarter scrren grab.PNG

 

 

 

 

1 ACCEPTED SOLUTION
alexei7
Continued Contributor
Continued Contributor

Hi,

 

I think the issue is in your filter for "Gift Amount".

 

You have Top N --> Top 5 --> By value "Sum of Gift Amount".

You want to change this to "Average of Gift Amount".

 

It looks to me as if the various $50,000 amount are being totalled and are greater than the $402k

 

Hope that helps

Alex

View solution in original post

8 REPLIES 8
alexei7
Continued Contributor
Continued Contributor

Hi,

 

That does sound odd. It sounds like something to do with the way the report is filtering

 

Is your Fiscal Year column part of the same table as Fiscal Quarter (maybe you have a date table with all these fields in)?

If it is, is that Fiscal Year value next to every date in your date table?

 

Alex

Yes, my Fiscal Year and Quarter are in the same date table and there is a FY value next to all dates, just double checked.

alexei7
Continued Contributor
Continued Contributor

Hmm, that's odd. 

It's quite difficult to tell without being to play about with your model, but I presume that's confidential.

 

Do all Fiscal Years selected return no results?

Is the join between Gift Date and between the Date in your Date table?

 

Alex

I removed confidential information and you should be able to view the file here.

alexei7
Continued Contributor
Continued Contributor

Thanks, really helpful!

 

When i'm filtering on Fiscal Year, it's definitely bringing back results - what is missing/what are you expecting to see that's different?

 

Alex

Sorry for the confusion, it always gave me results, but not the top 5. For example, if you click on FY Q2 in 2018, there is a gift of $402K in the top 5. But if you filter on the full fiscal year of 2018, that gift (while in the top 5), is skipped over.

alexei7
Continued Contributor
Continued Contributor

Hi,

 

I think the issue is in your filter for "Gift Amount".

 

You have Top N --> Top 5 --> By value "Sum of Gift Amount".

You want to change this to "Average of Gift Amount".

 

It looks to me as if the various $50,000 amount are being totalled and are greater than the $402k

 

Hope that helps

Alex

View solution in original post

Ah ha! Thanks @alexei7. That seems to be the trick.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors