Count regardless of Row Context

[Anonymous]

Hi,

I have a simple table with count of opportunities by created date and closed date. 

I want to create a table that shows the same number of opportunity that were created on that month regardless of when they closed. (The row should have the same number each column). So for example, in the right table, the row 2014 May should all have 376 each column, June 2014 should have 229 each column.

Right now I am using this formula: 

Denominator = CALCULATE(COUNTROWS(Opp),ALLSELECTED(Opp[CloseDate])).

[d_gosbell]

I'm assuming that in your screenshot that the columns across the page are created dates, and the rows are closed dates (based on the assumption that the columns start in 2013 and the rows start in 2014 and you can't close items before they are created).

So shouldn't your formula be the following:

Denominator = CALCULATE(COUNTROWS(Opp),ALLSELECTED(Opp[CreatedDate])).

Or have I misunderstood what values are on rows and columns in your matrix visual?

[Anonymous]

Thanks @d_gosbell for the reply. I have closedate in columns and created date in rows so it's the opposite. 

[d_gosbell]

So you are saying that the circled item below was created in May 2014, but closed over a year earlier in Jan 2013? That seems a bit strange.

I think the problem with this is that ALLSELECTED retains the row and column filter context so you probably want to use a calculation like the following using ALL to strip out all the filters then using VALUES to just bring back the CloseDate filters

Denominator = CALCULATE(COUNTROWS(Opp),ALL(Opp),VALUES(Opp[CloseDate]))

[Anonymous]

Haha yeah we have some serious data integrity issues. 

I tried the solution but it didn't work...

[d_gosbell · Accepted solution]

Sorry, I'd flipped my created and closed dates around as I assumed the data could not be correct the other way around...

So if it is CreateDate on the rows that is the column you'd want to bring back in using VALUES()

eg.

Denominator = CALCULATE(COUNTROWS(Opp),ALL(Opp),VALUES(Opp[CreateDate].[Date]))

[Anonymous]

Thank you! It worked!