Counting Problem

[nicoan]

Hello

I´m having problems counting properly with a calculated field. (Excel 2013 64b on win 7 64b)

The current calculated fields are:

[Max CLOSE 1 Week]
=IF([Sum of CLOSE]=BLANK(),BLANK(),(CALCULATE(MAX(FactTable[CLOSE]),DATESINPERIOD(Calendar[FullDate],LASTDATE(Calendar[FullDate])-1,-7,DAY))))

[1 Week New High]
=IF(SUM(FactTable[CLOSE])>[Max CLOSE 1 Week],1)

On table 1 the calculated fields are working fine on individual symbols.
On table 2 we need to count all the symbols for each date context that have a "1 Week New High" price, but it doesn´t work (is showing 1 for all days).
Also in "Value Field Settings" / "Summarize values as" everything is disabled for some reason.

The sample spreadsheet is here:
https://www.mediafire.com/?17v7scfbqr20cwd


How can we solve this?

 

[scottsen]

Clearly my friday nights are much too crazy to be downloading sample files, but I would think you want soething like:

=SUMX(VALUES(FactTable[Symbol]), [1 Week New High])

For all Symbols in the current context, evaluate the 1 week new high measure, and add up all the results.

 

[nicoan]

Scottsen, that fixed it, thanks so much.
You couldn´t believe how much you helped me so far..
BTW, congrats on your 500th post.


A quick follow up question:

I only want the percentage of the symbols that crosses these prices.
How could we reduce the amount of formulas needed to get the same results as "Percent 1W" to the minimum while keeping the efficiency?


Here´s an image of how it looks so far:

You probably won´t need this, but just in case, here´s the file:
https://www.mediafire.com/?l64qa9yhgjs1ywe



Here are all the formulas:

Max CLOSE 1 Week
=IF([Sum of CLOSE]=BLANK(),BLANK(),(CALCULATE(MAX(FactTable[CLOSE]),DATESINPERIOD(Calendar[FullDate],LASTDATE(Calendar[FullDate])-1,-7,DAY))))

1 Week New High
=IF(SUM(FactTable[CLOSE])>[Max CLOSE 1 Week],1)

Sum1w
=SUMX(VALUES(FactTable[Symbol]), [1 Week New High])

Percent 1W
=([Sum1w]*100)/[Count of SYMBOL]


Is it possible to have them all in one calculated field? I´m trying but can´t make it work.

 

[nicoan]

Please ignore the last message. Already fixed them in 2.

Thanks a mil.