Group by date & filter

[Anonymous]

Hello everyone,

I am new using Power Bi Desktop and am struggling with something...

I have imported a database from SQL table :

Each line corresponds to the detection of a car 1way/ car 2nd way/bike/truck/pedestrians

I am now trying to display the average number of total cars+trucks, filtered by day, filtered by zone (cameraID).

The idea would be to display an "average number of cars+trucks per day in february in zone 4" for example.

I tryed using the group by function to make a new table with merged dates but can't make it work because of the differences between zones and type of detection.

Any idea how to solve this problem please ?

Kind regards,

Antoine

[Greg_Deckler · Accepted solution]

So, use slicers for what you want to filter on. Then you need a Date column with Date and no timestamp or Date and the same timestamp. Then you should be able to use SUMMARIZE like:

Average Measure = 
  VAR __Table = 
    SUMMARIZE(
      'Table',
      [Date no Timestamp],
      "Count",COUNTROWS('Table')
    )
RETURN
  AVERAGEX(__Table,[Count])

[Anonymous]

It works ! Thanks very much for your help 🙂