I want to create a dax formula which returns the best performing day in Power Bi.

[Saud_Jilani]

To simplify I need to use the Max formula as Max(1,2,3) where it returns 3. But it is not possible, so is there is a way around it. So I have sales in a column and day name in the other. I need the day with the highest total sales. Here is the way I tried to achieve it:

Best Day =
            Var Sun = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Sunday")

            Var Mon = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Monday")

            Var Tues = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Tuesday")

            Var Wed = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Wednesday")

            Var Thurs = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Thursday")

            Var Fri = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Friday")

            Var Sat = CALCULATE(SUM('Sales (2)'[Sales]), 'Dax Calendar'[Day Name] = "Saturday")

RETURN

MAX(Sun,Mon,Tues,Wed,Thurs,Fri,Sat)

[Ritaf1983 · Accepted solution]

Hi @Saud_Jilani 
You can create 2 measures :

1 for highest sales :

Highest sales =

MAXX(

    SUMMARIZE (

        Sales,

      'calendar'[Date],

        "Total sales", SUM('Sales'[sales_])

    ),

    [Total sales]

)

second for the day of highest sales 

Highest day =

VAR vTable = VALUES('calendar'[WeekDay] )

VAR highestSales = [Highest sales]

VAR Keepmin = FILTER( vTable, [Highest sales] = highestSales )

VAR HighestDay = IF( highestSales >0, CONCATENATEX( Keepmin, [WeekDay], ", "))

RETURN

HighestDay

Result:

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Ritaf1983 · Accepted solution]

Hi @Saud_Jilani 
You can create 2 measures :

1 for highest sales :

Highest sales =

MAXX(

    SUMMARIZE (

        Sales,

      'calendar'[Date],

        "Total sales", SUM('Sales'[sales_])

    ),

    [Total sales]

)

second for the day of highest sales 

Highest day =

VAR vTable = VALUES('calendar'[WeekDay] )

VAR highestSales = [Highest sales]

VAR Keepmin = FILTER( vTable, [Highest sales] = highestSales )

VAR HighestDay = IF( highestSales >0, CONCATENATEX( Keepmin, [WeekDay], ", "))

RETURN

HighestDay

Result:

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Saud_Jilani]

Thanks @Ritaf1983 , its perfect.
Any idea on how to get the 2nd best day as well? 

[Ritaf1983 · Accepted solution]

Hi @Saud_Jilani 
For the highest second day, you can use these formulas :

Highest sales 2 =

MINX( TOPN(2,

    SUMMARIZE (

        Sales,

      'calendar'[WeekDay],

        "Total sales",SUM('Sales'[sales_])

    ),[Total sales],DESC)

,[Total sales])

And for the day :

Highest day 2 =

VAR vTable = VALUES('calendar'[WeekDay] )

VAR highestSales2 = [Highest sales 2]

VAR Keepmin = FILTER( vTable, [Highest sales 2] = highestSales2 )

VAR HighestDay2 = IF( highestSales2 >0, CONCATENATEX( Keepmin, [WeekDay], ", "))

RETURN

HighestDay2

Result:

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @Saud_Jilani - create below measure to calculate the highest total sales, I hope you have a day name column in your dataset.

 Measure:

Best Day Sales =
MAXX(
VALUES('DaySales'[Day Name]), -- Iterate over each unique day name
CALCULATE(SUM('DaySales'[Total Sales])) -- Calculate total sales for each day
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Saud_Jilani]

Thanks, @rajendraongole1, it works well.
How can modify it to result as:
The Highest day is "x" with the sales"$x" and the 2nd highest day is "y" with the sales "$y" . 
By "$x" I refer the amount, and "x" refers to the corresponding day.