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

     

Anonymous's avatar
Anonymous
Not applicable
11 months ago

Hi Anonymous .

Thanks danextian  for jumping in with the detailed solution using INDEX and PARTITIONBY. That aligns perfectly with what they asked for having separate measures for Daily and Weekly that always return the 2nd latest date enrollment totals, even outside of a table visual.

Anonymous  were you able to try out those measures in your report and confirm if they give you the expected “Yesterday” and “Last Week” values?

Thanks,
Akhil.