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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
benjec83
Regular Visitor

Count results by date and location

I'm have a table of results of individual sales across 12 locations. I'm looking for a way to count how many sales per location were made, by date. There's usually betwen 200 - 500 sales per day, per location.

 

I've made an small example of how my data comes, and what I'm hoping to achieve.

 

Example Data

data.jpg

 

 

 

 

 

 

 

 

 

 

 

Example outcome

result.png.jpg

1 ACCEPTED SOLUTION
BhaveshPatel
Community Champion
Community Champion

Hi @benjec83

 

Use Groupby in the Query editor to get the correct results.

 

Groupby column : 

Location

Date

 

Operation

 

Count Rows

 

This will give you the correct results.

 

Alternatively, You can write a measure of COUNTROWS to achieve what is desired.

 

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

All you really need is a simple MEASURE => Sales Per Location = COUNTROWS ( 'TableName' )

 

then create a Table Visualization with the date, location and the Measure - you will get the result you want

 

you can alos create a Matirx Visualization with Date and Location in the Rows and the Measure in the Values

 

or alternatively Date in the Rows, Location in the Columns and the Measure in the Values

 

TableMatrix - COUNTROWS.png

 

 

Anonymous
Not applicable

Sean, Thanks for the Measure, My table has multiple columns and I'd like to count the rows based on each column (All columns have few blank cells which corresponds to the date stamp). Is there a way for me to get a row count based on different columns in a same table? @Sean 

BhaveshPatel
Community Champion
Community Champion

Hi @benjec83

 

Use Groupby in the Query editor to get the correct results.

 

Groupby column : 

Location

Date

 

Operation

 

Count Rows

 

This will give you the correct results.

 

Alternatively, You can write a measure of COUNTROWS to achieve what is desired.

 

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Thanks - this sorted my problem!

 

My next question - sorry, I'm pretty new to this, is that I want to add a column in that will put in specific sales targets. They are different per location, and different per day. I need to look up the value, but it's based on 2 qualifiers from the existing date - date and location

I'm using Target = Locatioin&" "&Date in both tables to make it only one column to reference - but if there is a better way, I'm open to it

You can use a Conditional Column in Query Editor to achieve this or

 

Alternatively, You can use SWITCH or IF function to set your target as per the different day and different location.

 

Visit this BLOG to get the idea on how to use SWITCH function.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.