Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
11 months ago
Solved

Max value with a subcategory

Hello. I have data that looks like the data inserted below. I am trying to sum the enrollment on the 2nd largest begin date for each ag_level.   For example (using the data sample below), for ag_...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    11 months ago

    Anonymous 

    you can create a calculated column

     

    column=
    var _max=CALCULATE(max('Table'[begin date]),ALLEXCEPT('Table','Table'[ag_level]))
    return if('Table'[begin date]=maxx(FILTER('Table','Table'[ag_level]=EARLIER('Table'[ag_level])&&'Table'[begin date]<_max),'Table'[begin date]),1)
     
     
    filter the column to 1, then no matter you choose daily or weekly , it will only show the data that you want.
  • danextian's avatar
    danextian
    11 months ago

    Hi Anonymous 

     

    Try the following measures:

    All Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

    Daily Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate && 'Table'[ag_level] = "Daily" ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

    Weekly Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate && 'Table'[ag_level] = "Weekly" ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

    Details are in the attached pbix

     

ryan_mayu's avatar
ryan_mayu
Super User
11 months ago

Anonymous 

you can create a calculated column

 

column=
var _max=CALCULATE(max('Table'[begin date]),ALLEXCEPT('Table','Table'[ag_level]))
return if('Table'[begin date]=maxx(FILTER('Table','Table'[ag_level]=EARLIER('Table'[ag_level])&&'Table'[begin date]<_max),'Table'[begin date]),1)
 
 
filter the column to 1, then no matter you choose daily or weekly , it will only show the data that you want.
Anonymous's avatar
Anonymous
Not applicable
11 months ago

Hi.  Thank you.  I may be able to use this in another dashboard, but my current one needs to have the weekly and daily separated so I can show both simultaneously in the view, as shown by Irwan  below .  However, I'm curious, because I did try to build your formula (for use later), but the portion 'Table'[begin date]= will not accept the column "begin date".  It appears to want a measure and not a column in that spot of the code.

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    11 months ago

    what's the expected output? only display the date? could you pls clarify this?