Good morning Power BI community!
I'm running into some problem building my model, and I was wondering is you guys could help.
I want to have a Card that gives me the average number of rentals per station.
However, the number of stations that I have is not fixed. It increased over time.
Moreover, I want to be able to filter by region/town on top of date filtering.
And here is the rentals table:
I already use this measure to filter by date, but filtering by town/region does not seem to work correctly.
Measure = SUMX ( SUMMARIZE ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ), CALCULATE ( DIVIDE ( COUNTROWS ( Rentals ), COUNTROWS ( FILTER ( ALL ( Stations ), Stations[Opening date] <= MAX ( 'Calendar'[Date].[Date] ) ) ) ) ) )
Can anyone help me with that?
Thank you very much!
You may refer to below measure:
Measure = SUMX ( SUMMARIZE ( 'calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ), CALCULATE ( DIVIDE ( COUNTROWS ( Rentals ), CALCULATE ( COUNTROWS ( Stations ), FILTER ( ALLEXCEPT ( Stations, Stations[Region] ), MONTH ( Stations[Opening date] ) <= MONTH ( MAX ( Stations[Opening date] ) ) && Stations[Opening date] <= MAX ( 'calendar'[Date].[Date] ) ) ) ) ) )