Min/Max column Chart

[rodrigoestrella]

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

[v-jiascu-msft · Accepted solution]

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

[v-jiascu-msft]

Hi @rodrigoestrella,

Did these posts solve your issue?

Best Regards,

Dale

[rodrigoestrella]

It worked! Thanks a lot guys!

[v-jiascu-msft · Accepted solution]

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]

Sure Dale, thanks for replying 

StoreKey	Date	Clients	Articles	Ticket Time	art. per minute	Open Box Time	Sales
2	20171101	1774	20945	2932,13	6,44	4069,4	641205,26
3	20171101	460	3458	487,25	6,16	838,15	85529,63
4	20171101	764	6848	1023,38	5,87	1449,67	206713,44
5	20171101	4	6	11,28	0,52	17,17	5926
6	20171101	659	6755	1053,1	5,81	1445,33	200290,93
7	20171101	930	12902	1841,42	6,41	2479,7	412736,77
8	20171101	532	6599	859,07	6,93	1290,1	196952,02
9	20171101	826	12215	1589,72	7,07	2145,53	419640,5
10	20171101	696	6699	855,37	6,9	1204,83	163472,46

[Anonymous · Accepted solution]

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]))