Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rodrigoestrella
Frequent Visitor

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

 

 

2 ACCEPTED SOLUTIONS
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

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

2018-06-06_10-18-42.png

 

 

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

2018-06-06_10-18-57.png

 

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @rodrigoestrella,

 

Did these posts solve your issue?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It worked! Thanks a lot guys!
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Not applicable

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

2018-06-06_10-18-42.png

 

 

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

2018-06-06_10-18-57.png

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.