Count of Rows with and without condition in columns

[Anonymous]

I'm looking have a combined view of a count of all rows, and then a count of rows that meet a criteria. 

In this example I want to bucket by the "Stage" value. I will only need a count and sum of rev, but will need to filter and drill down - which is why a combined view is important. 

Source:

ID	Sub-ID	Stage	Rev
A	Direct	0	$100
B	Indirect	0	$125
C	Indirect	1	$150
D	Direct	3	$175
E	Direct	5	$75
F	Indirect	6	$100
G	Direct	6	$50

Returned Matrix:

[sturlaws · Accepted solution]

Hi@Anonymous 

I would first create a new table like this:

Stage bucket = DATATABLE("Bucket";STRING;{{"All"};{"1-5"};{"6"}})

Add 'Stage Bucket'[Bucket] to the columns of a matrix visualization.

Then create this measure

Number# = 
var sv=SELECTEDVALUE('Stage bucket'[Bucket])
return
SWITCH(
    true();
    sv="All";CALCULATE(COUNT('Table'[ID]);ALL('Table'[Stage]));
    sv="1-5";CALCULATE(COUNT('Table'[ID]);filter('Table';'Table'[Stage]<=5 && 'Table'[Stage]>0));
    sv="6";CALCULATE(COUNT('Table'[ID]);filter('Table';'Table'[Stage]=6));
    blank()
)

and this one

revenue = 
var sv=SELECTEDVALUE('Stage bucket'[Bucket])
return
SWITCH(
    true();
    sv="All";CALCULATE(sum('Table'[Rev]);ALL('Table'[Stage]));
    sv="1-5";CALCULATE(sum('Table'[Rev]);filter('Table';'Table'[Stage]<=5 && 'Table'[Stage]>0));
    sv="6";CALCULATE(sum('Table'[Rev]);filter('Table';'Table'[Stage]=6));
    blank()
)

Now add both these measures to the matrix visual as values. And then add Sub-ID and ID to the rows of the matrix visualization. This should give you a matrix looking like this:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hey @sturlaws, 

I have another question on this.

I'm trying to filter this with a date slider using a date table. I have two measures [Date Min] and [Date Max] that I use in conjunction with DATESBETWEEN() to help me filter. 

The code looks as follows:

Number# = 
var sv=SELECTEDVALUE('Stage bucket'[Bucket])
return
SWITCH(
    true();
    sv="All",CALCULATE(COUNT('Table'[ID]),ALL('Table'[Stage]),DATESBETWEEN('Table'[Date1],[Date Min],[Date Max])),
    sv="1-5",CALCULATE(COUNT('Table'[ID]),filter('Table';'Table'[Stage]<=5 && 'Table'[Stage]>0),DATESBETWEEN('Table'[Date2],[Date Min],[Date Max])),
    sv="6",CALCULATE(COUNT('Table'[ID]),filter('Table';'Table'[Stage]=6)DATESBETWEEN('Table'[Date2],[Date Min],[Date Max])),
    blank()
)

Now, I get the right numbers returned for "All" and "6", but I am not able to get the right one for "1-5".

Without the date filter the values are correct, so the first part is working. I'm not sure why two of them are working, but the other is not. 

Any thoughts?

[sturlaws]

Yes 😃

First I would create two new tables, vDate and vStage. vDate you can create using the CALENDAR-function:

=CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2019, 12, 31 ) )

just choose apropriate start and end dates.

For vStage you can use the 'Enter Data'-functionality in Power BI, you find it in the Home ribbon. Just enter the numbers 0,1,2,3,4,5,6 in the table, and name the column Stage.

Look at the schema view of your model. If the relationships are not created automatically, create relationship between vDate and Table on Date, and between vStage and Table on Stage.

then write this measure

Number# 2 =
VAR _sv =
    SELECTEDVALUE ( 'Stage bucket'[Bucket] )
VAR _minDate =
    MIN ( vDate[Date] )
VAR _maxDate =
    MAX ( vDate[Date] )
RETURN
    SWITCH (
        TRUE (),
        _sv = "All", CALCULATE (
            COUNT ( 'Table'[ID] ),
            ALL ( 'vStage' ),
            FILTER ( ALL ( vDate ), 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate )
        ),
        _sv = "1-5", CALCULATE (
            COUNT ( 'Table'[ID] ),
            FILTER ( ALL ( vStage ), 'vStage'[Stage] <= 5 && 'vStage'[Stage] > 0 ),
            FILTER ( ALL ( vDate ), 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate )
        ),
        _sv = "6", CALCULATE (
            COUNT ( 'Table'[ID] ),
            FILTER ( ALL ( vStage ), 'vStage'[Stage] = 6 ),
            FILTER ( ALL ( vDate ), 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate )
        ),
        BLANK ()
    )

Make sure you use date from vDate as value on your time slicer

Cheers,
Sturla

[Anonymous]

Thanks for the reply @sturlaws , I really appreciate your help.

I think this may solve one of my issues, but there is something I don't know if I made clear. 

For "All", "1-5" and "6" I'm actually filtering on different date columns in the same Table.

I know it's confusing, but for context this is analyzing pipeline, so imagine, "Created Date", "Active Pipeline Date" and "Close Date" 

[sturlaws]

confusing indeed.

Could you provide a sample dataset? And a mockup of your desired outcome?

[Anonymous]

Hey @sturlaws, the following is a mock dataset I put together. I've got a lot of exclusions and relationships that make it complicated but I think this will suffice. This example is only looking for the count, not the sum of rev like above. 

Source:

Date 1	Date 2	Stage
1/25/2019	2/14/2019	6
1/25/2019	2/21/2019	4
2/16/2019	4/13/2019	2
3/16/2019	4/10/2019	2
2/18/2019	3/7/2019	3
2/10/2019	2/24/2019	5
2/21/2019	4/18/2019	0
3/19/2019	4/11/2019	3
4/3/2019	6/7/2019	1
4/14/2019	5/10/2019	0
5/8/2019	5/20/2019	2
4/25/2019	5/23/2019	5
3/31/2019	6/6/2019	6
4/11/2019	4/26/2019	6
5/7/2019	5/11/2019	3
5/22/2019	5/23/2019	0
6/2/2019	6/10/2019	2
6/26/2019	7/16/2019	1
5/12/2019	5/28/2019	3
5/23/2019	6/16/2019	0
6/18/2019	7/21/2019	1
7/3/2019	7/31/2019	0
7/14/2019	7/16/2019	5
8/3/2019	8/28/2019	6
7/8/2019	7/25/2019	3
6/30/2019	7/14/2019	1
7/11/2019	9/5/2019	2
8/6/2019	8/29/2019	1
8/21/2019	10/25/2019	6
9/1/2019	9/27/2019	1
9/25/2019	10/7/2019	4
9/12/2019	10/10/2019	5
8/18/2019	10/24/2019	0
8/29/2019	9/13/2019	2
9/24/2019	9/28/2019	1
10/9/2019	10/10/2019	4
10/20/2019	10/28/2019	3
11/13/2019	12/3/2019	2
9/29/2019	10/15/2019	1
10/10/2019	11/3/2019	2
11/5/2019	12/8/2019	1
10/24/2019	11/8/2019	0
11/19/2019	11/23/2019	2
12/4/2019	12/5/2019	1
12/15/2019	12/23/2019	2

If filtering from between 3/1/2019 and 10/31/2019, expected result:

All	"1-5"	"6"
34	25	4

"All" is using Date 1 as only filter

"1-5" where stage is between 1-5 and using Date 2
"6" where stage is 6 using Date 2

Let me know if that doesn't make sense, and again thanks for the help.

[sturlaws]

I created a new table in the model with this sample data set, and called it NewData(not very imaginative, I know). Make sure the columns are of Date data type. Create connections to vDate[Date] with both 'NewData'[Date1] and 'NewData'[Date 2]. Also create the relationship with vStage.

Then create this measure:

Number# 3 =
VAR _sv =
    SELECTEDVALUE ( 'Stage bucket'[Bucket] )
VAR _minDate =
    MIN ( vDate[Date] )
VAR _maxDate =
    MAX ( vDate[Date] )
RETURN
    SWITCH (
        TRUE ();
        _sv = "All"; CALCULATE (
            COUNTROWS ( 'NewData' );
            ALL ( 'vStage' );
            FILTER ( ALL ( vDate ); 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate );
            USERELATIONSHIP ( NewData[Date 1]; vDate[Date] )
        );
        _sv = "1-5"; CALCULATE (
            COUNTROWS ( 'NewData' );
            FILTER ( ALL ( vStage ); 'vStage'[Stage] <= 5 && 'vStage'[Stage] > 0 );
            FILTER ( ALL ( vDate ); 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate );
            USERELATIONSHIP ( NewData[Date 2]; vDate[Date] )
        );
        _sv = "6"; CALCULATE (
            COUNTROWS ( 'NewData' );
            FILTER ( ALL ( vStage ); 'vStage'[Stage] = 6 );
            FILTER ( ALL ( vDate ); 'vDate'[Date] >= _minDate && vDate[Date] <= _maxDate );
            USERELATIONSHIP ( NewData[Date 1]; vDate[Date] )
        );
        BLANK ()
    )

[Anonymous]

Worked great! 

Thanks @sturlaws!