Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count of Rows with and without condition in columns

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:

IDSub-IDStageRev
ADirect0$100
BIndirect0$125
CIndirect1$150
DDirect3$175
EDirect5$75
FIndirect6$100
GDirect6$50

 

Returned Matrix:

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    HiAnonymous 

     

    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.

8 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    HiAnonymous 

     

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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