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

     

Irwan's avatar
Irwan
Super User
11 months ago

hello Anonymous 

 

please check if this accomodate your need.

create two measures for [Yesterday] and [Last Week]

Yesterday =
var _MaxDate = 
CALCULATE(
    MAX('Table'[begin date]),
    ALLEXCEPT('Table','Table'[ag_level])
)
var _Yesterday = 
CALCULATE(
    SUM('Table'[enrolled_count]),
    FILTER(
        ALL('Table'),
        'Table'[ag_level]="Daily"&&
        'Table'[begin date]=_MaxDate-1
    )
)
Return
IF(
    SELECTEDVALUE('Table'[ag_level])="Daily"&&MAX('Table'[begin date])=_MaxDate,
    _Yesterday
)
Last Week =
var _MaxDate = 
CALCULATE(
    MAX('Table'[begin date]),
    ALLEXCEPT('Table','Table'[ag_level])
)
var _MaxWeek = WEEKNUM(_MaxDate)
var _Format = YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(_MaxWeek,"00")
var _LastFormat = YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(_MaxWeek-1,"00")
var _LastWeek = 
CALCULATE(
    SUM('Table'[enrolled_count]),
    FILTER(
        ALL('Table'),
        'Table'[ag_level]="Weekly"&&
        YEAR('Table'[begin date])&FORMAT(WEEKNUM('Table'[begin date]),"00")=_LastFormat
    )
)
Return
IF(
    SELECTEDVALUE('Table'[ag_level])="Weekly"&&YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(WEEKNUM(SELECTEDVALUE('Table'[begin date])),"00")=_Format,
    _LastWeek
)
that _Format is for preventing wrong value when changing year (from week 52 to week 1)
 
Hope this will help.
Thank you.
Anonymous's avatar
Anonymous
Not applicable
11 months ago

Thank you Irwan .  I see that this works when I review your pbix file.  However, it appears to be dependent on including rows in the visual.  That is, if I only want to show the value of "yesterday" (and not show any other fields), it displays as empty.  Is that because it uses "selected value" ?  I was trying to figure out how "selected value" comes into play.  Thank you again!

 

  • Irwan's avatar
    Irwan
    Super User
    11 months ago

    hello Anonymous 

     

    since measure is based on filter context, then to make measure work you need a filter.

     

    as you mentioned, when you all fields, the filter is no longer there to make the measure worked. As the result, your display is empty or blank.

     

    selectedvalue only for conditional if, so the value is separated between DAILY and WEEKLY.

     

    if you want show Yesterday and Last Week without any filter, then you need to make Yesterday and Last Week as a calculated column.

     

    Hope this will help.

    Thank you.