Forum Discussion

diego-jd's avatar
diego-jd
Frequent Visitor
2 years ago
Solved

Card with two Top N FIlters

Hi, I have my data with this structure. This is daily data of multiple dealers and sale data:
   Dealer Name
   Sales
   upload_datetime

I want to create a Card with the top sales of the latest upload_datetime. E.g. the dealer with the most sales at the latest date.

 

I already have a Card with Top 1 sales but it takes the sum of sales for every day. I tried putting another Top N filter for upload_datetime but it doesn't let me. Also, a relative filter (like last 1 day) wouldn't work either because even though the data is daily, it doesn't updates every day.


Any ideas? Thank you

Need Help
  • Alex87's avatar
    Alex87
    2 years ago

    Hello! 

    Give a try to this DAX measure and if it matches your expectations, please mark it as a solution. Cheers!

     

    Top Sales Latest Date = 
VAR LatestDate = MAX('Dealers'[upload_datetime])
VAR TopSalesOnLatestDate = 
    CALCULATE(
        MAX('Dealers'[Sales]),
        'Dealers'[upload_datetime] = LatestDate
    )
VAR TopDealerOnLatestDate = 
    CALCULATE(
        FIRSTNONBLANK('Dealers'[Dealer Name], TopSalesOnLatestDate),
        'Dealers'[upload_datetime] = LatestDate,
        'Dealers'[Sales] = TopSalesOnLatestDate
    )
RETURN
    TopDealerOnLatestDate
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, diego-jd 

    You can refer to Alex87  reply, if it does not work, you can refer to following Measure.

     

    maxSales =
VAR _latestDate =
    CALCULATE ( MAX ( 'Table'[Upload_datetime] ) )
VAR _maxSales =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Upload_datetime] = _latestDate )
    )
VAR _dealerName =
    CALCULATE (
        MAX ( 'Table'[Dealer Name] ),
        FILTER (
            'Table',
            'Table'[Sales] = _maxSales
                && 'Table'[Upload_datetime] = _latestDate
        )
    )
RETURN
    _maxSales & " " & _dealerName

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Alex87's avatar
    Alex87
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello! 

    Give a try to this DAX measure and if it matches your expectations, please mark it as a solution. Cheers!

     

    Top Sales Latest Date = 
VAR LatestDate = MAX('Dealers'[upload_datetime])
VAR TopSalesOnLatestDate = 
    CALCULATE(
        MAX('Dealers'[Sales]),
        'Dealers'[upload_datetime] = LatestDate
    )
VAR TopDealerOnLatestDate = 
    CALCULATE(
        FIRSTNONBLANK('Dealers'[Dealer Name], TopSalesOnLatestDate),
        'Dealers'[upload_datetime] = LatestDate,
        'Dealers'[Sales] = TopSalesOnLatestDate
    )
RETURN
    TopDealerOnLatestDate
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, diego-jd 

    You can refer to Alex87  reply, if it does not work, you can refer to following Measure.

     

    maxSales =
VAR _latestDate =
    CALCULATE ( MAX ( 'Table'[Upload_datetime] ) )
VAR _maxSales =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Upload_datetime] = _latestDate )
    )
VAR _dealerName =
    CALCULATE (
        MAX ( 'Table'[Dealer Name] ),
        FILTER (
            'Table',
            'Table'[Sales] = _maxSales
                && 'Table'[Upload_datetime] = _latestDate
        )
    )
RETURN
    _maxSales & " " & _dealerName

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum