Count results by date and location

[benjec83]

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

Example outcome

[Sean]

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

[Anonymous]

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 · Accepted solution]

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.

[benjec83]

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

[BhaveshPatel]

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.