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

Count regardless of Row Context

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])).
 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Member
Member

Re: Count regardless of Row Context

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]))
6 REPLIES 6
d_gosbell Member
Member

Re: Count regardless of Row Context

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?

Jaeeunc Frequent Visitor
Frequent Visitor

Re: Count regardless of Row Context

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

d_gosbell Member
Member

Re: Count regardless of Row Context

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.

2019-03 closed vs created.png

 

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]))
Highlighted
Jaeeunc Frequent Visitor
Frequent Visitor

Re: Count regardless of Row Context

Haha yeah we have some serious data integrity issues. 

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

Capture.JPG

d_gosbell Member
Member

Re: Count regardless of Row Context

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]))
Jaeeunc Frequent Visitor
Frequent Visitor

Re: Count regardless of Row Context

Thank you! It worked!