4 Replies Latest reply on Jun 20, 2017 8:22 PM by Wilson Po

Add FY giving as calculated measure for text table?

I am still trying to find the best way to come up with a calculated measure that would allow me to show the sum of gifts made by an individual within a fiscal year, and display that in a text table.  We have a fiscal year dimension, which I think should help.  I am so close, and yet...

As you will see in the attached workbook, due to the join structure of our data, the gift data is duplicated for "Pete".  As a result, the FY16 and FY17 calculations that I came up with show twice the amount that he actually gave.  I calculated each of these differently, but still couldn't make it work.  I thought the FY17 calculation would do it, but no such luck.

If anyone has an idea on how to make this work, I would greatly appreciate it!

• 1. Re: Add FY giving as calculated measure for text table?

Hi Amy,

Try nesting the logic statement within the LOD:

ZN({FIXED [GiftID]:MAX(IF [fiscyear] = "2017" then [Amount] END)})

This should produce the right results for FY17.  I think the order of operation still produced some aggregation of the data across the years that was not wanted;  this filters out FY16 first and then computes the results.

1 of 1 people found this helpful
• 2. Re: Add FY giving as calculated measure for text table?

I think that got it, Wilson!  Thank you SO much.  Now I get to test it in the BIG workbook.

Eta:  That did it.  AWESOME.  Thank you very much!

amy

• 3. Re: Add FY giving as calculated measure for text table?

Wilson, I'm hoping you'll see this...  you very successfully helped me determine how to create measures for FY giving (thank you again!).  Now I need to create unique measures for each year's number of gifts.  I'm sure it's fairly simple, but right now my brain is completely fried!  Any ideas you might have would be greatly appreciated.

amy

• 4. Re: Add FY giving as calculated measure for text table?

Hey Amy,

The duplicate issue in the data shouldn't actually be a problem for counting distinct number of gifts.  The COUNTD() should remove the effect of most duplicates in the data and produce the correct results:

COUNTD(IF [fiscyear] = "2017" then [GiftID] END)

Hope this helps