Forum Discussion

rodrigoestrella's avatar
rodrigoestrella
Frequent Visitor
8 years ago
Solved

Min/Max column Chart

Hello everyone!

I need to make a measure that shows me the day of maximum average clients for each store.

In a table I have the clients for each day of the month for each store. And I need to make a table that shows me the day that the store receives the max number of clients on average.

 

This are the columns of my tables.

clients per day

Date | storekey | clients

 

Week

dayoftheweek | dayname | average of clients

 

So I would like to get a table that shows me this

 

Store | dayname with max average of clients

 

For example. 

Central Store | Saturday | 1200 (clients)

 

I hope I´m being clear. If not, let me know.

Thanks in advance! 

Rodrigo

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi rodrigoestrella,

     

    Can you share a dummy sample?

    It could be like below.

    table =
SUMMARIZE (
    week,
    'clients per day'[sotrekey],
    week[dayname],
    "maxnum", MAX ( week[average of clients] )
)

    Best Regards,

    Dale

  • Anonymous's avatar
    Anonymous
    8 years ago

    For your data, the orignial type of column Date is number, not the standard DAX datatime format. If you want to get Month and Weekday information from this column, first, you need to use the following DAX to convert the number to datatime.

     

    DateTime = DATE(INT(LEFT(Store[Date],4)),INT(MID(Store[Date],5,2)),INT(RIGHT(Store[Date],2)))

    And then create two another calcuated columns to mark the Month and Weekday information

     

    Month = FORMAT(Store[DateTime],"MMMM")
    Weekday = FORMAT(Store[DateTime],"DDDD")

     

     

    After that, you can create a calcuated table and using the following DAX as Dale's suggestion

     

    StoreSummary = SUMMARIZE(Store,Store[StoreKey],Store[Month],Store[WeekDay],"Max",MAX(Store[Clients]))

     

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi rodrigoestrella,

     

    Can you share a dummy sample?

    It could be like below.

    table =
SUMMARIZE (
    week,
    'clients per day'[sotrekey],
    week[dayname],
    "maxnum", MAX ( week[average of clients] )
)

    Best Regards,

    Dale

    • rodrigoestrella's avatar
      rodrigoestrella
      Frequent Visitor
      8 years ago

      Sure Dale, thanks for replying 

      StoreKeyDateClientsArticlesTicket Timeart. per minuteOpen Box TimeSales 
      2201711011774209452932,136,444069,4641205,26 
      3201711014603458487,256,16838,1585529,63 
      42017110176468481023,385,871449,67206713,44 
      5201711014611,280,5217,175926 
      62017110165967551053,15,811445,33200290,93 
      720171101930129021841,426,412479,7412736,77 
      8201711015326599859,076,931290,1196952,02 
      920171101826122151589,727,072145,53419640,5 
      10201711016966699855,376,91204,83163472,46 
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        For your data, the orignial type of column Date is number, not the standard DAX datatime format. If you want to get Month and Weekday information from this column, first, you need to use the following DAX to convert the number to datatime.

         

        DateTime = DATE(INT(LEFT(Store[Date],4)),INT(MID(Store[Date],5,2)),INT(RIGHT(Store[Date],2)))

        And then create two another calcuated columns to mark the Month and Weekday information

         

        Month = FORMAT(Store[DateTime],"MMMM")
        Weekday = FORMAT(Store[DateTime],"DDDD")

         

         

        After that, you can create a calcuated table and using the following DAX as Dale's suggestion

         

        StoreSummary = SUMMARIZE(Store,Store[StoreKey],Store[Month],Store[WeekDay],"Max",MAX(Store[Clients]))