TOPN Values and MAX Date

[DR2022]

Hi! How to return the highest values and itens, for example the top 5, in the current date (last dste), using the TOPN and MAX functions?

[TomMartens · Accepted solution]

Hey @DR2022 ,

this measure returns the last date (within the given filter context) inside a fact table exists:

LastDate = 
MAXX(
    SUMMARIZE(
        'FactOnlineSales'
        , 'DimDate'[Datekey]
    )
    , 'DimDate'[Datekey]
)

This date then is used to filter a virtual table using FILTER(SUMMARIZE(...), ...), this table is nested inside the TOPN function.
Finally, the new INDEX function is used to find the  4th value, the same approach can be used to find the product key, only the last line in the SELECTCOLUMNS has to be changed to reference the productkey column:

GetTheNthValueFromTheTopNList = 
var NthValue = 4
var topnItems = 5
var theLastDate = [LastDate]
var theTopNTable = 
    TOPN(
        topnItems
        , CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    'FactOnlineSales'
                    , 'DimProduct'[ProductKey]
                    , 'DimDate'[Datekey]
                )
                , "v" , CALCULATE( SUM( 'FactOnlineSales'[SalesQuantity] ) )
            )
            , 'DimDate'[Datekey] =  theLastDate
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesQuantity] ) )
        , DESC
    )
var theNValue =
SELECTCOLUMNS(
    iNDEX(
        NthValue
        , theTopNTable
        , ORDERBY( [v] , DESC)
        , KEEP
    )
    , [v]
)
return
theNthValue

The next picture shows separate card visuals, the left shows the last date, the one in the middle visualizes the TOPN table using the DAX function TOJSON(...), and the rightmost visual shows the nth value.


Hopefully, this provides what you are looking for,

Regards,

Tom

[AlB · Accepted solution]

Hi @DR2022 

You can place the sales values in a table visual, select the current date in a slicer and  use a filter visual to only  show the top 5

Check this out https://community.powerbi.com/t5/Desktop/TopN/m-p/131646

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[TomMartens · Accepted solution]

Hey @DR2022 ,

this measure returns the last date (within the given filter context) inside a fact table exists:

LastDate = 
MAXX(
    SUMMARIZE(
        'FactOnlineSales'
        , 'DimDate'[Datekey]
    )
    , 'DimDate'[Datekey]
)

This date then is used to filter a virtual table using FILTER(SUMMARIZE(...), ...), this table is nested inside the TOPN function.
Finally, the new INDEX function is used to find the  4th value, the same approach can be used to find the product key, only the last line in the SELECTCOLUMNS has to be changed to reference the productkey column:

GetTheNthValueFromTheTopNList = 
var NthValue = 4
var topnItems = 5
var theLastDate = [LastDate]
var theTopNTable = 
    TOPN(
        topnItems
        , CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    'FactOnlineSales'
                    , 'DimProduct'[ProductKey]
                    , 'DimDate'[Datekey]
                )
                , "v" , CALCULATE( SUM( 'FactOnlineSales'[SalesQuantity] ) )
            )
            , 'DimDate'[Datekey] =  theLastDate
        )
        , CALCULATE( SUM( 'FactOnlineSales'[SalesQuantity] ) )
        , DESC
    )
var theNValue =
SELECTCOLUMNS(
    iNDEX(
        NthValue
        , theTopNTable
        , ORDERBY( [v] , DESC)
        , KEEP
    )
    , [v]
)
return
theNthValue

The next picture shows separate card visuals, the left shows the last date, the one in the middle visualizes the TOPN table using the DAX function TOJSON(...), and the rightmost visual shows the nth value.


Hopefully, this provides what you are looking for,

Regards,

Tom