Filter Count

[ValentinBIA]

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.

Station_ID	Opening date
A	1/1/2018
B	1/1/2018
C	2/1/2018
D	4/1/2018
E	4/1/2018
F	5/1/2018

And here is the rentals table:

Rental_ID	Date
1	5/1/2018
2	9/1/2018
3	20/1/2018
4	20/1/2018
5	22/1/2018
6	29/1/2018
7	2/2/2018
8	5/2/2018
9	5/2/2018
10	25/2/2018
11	9/4/2018
12	11/4/2018
13	21/4/2018
14	3/5/2018
15	13/5/2018
16	15/5/2018
17	15/5/2018
18	19/5/2018
19	25/5/2018
20	25/5/2018

What I currently have is measure that gives me the average number of rentals by stations, but considers that there are 6 stations at all times.

Avg_rentals = DIVIDE(COUNT(Rentals[Rental_id]),COUNTX(Stations,Stations[Station_ID))

I use slicers to filter rentals by date (and also by city, but not explained in this example).

For example is if I select date from 1/1/2018 to 3/1/2018 (US format), I want the measure to give me the result:

Average rentals = 4.33 (which is 6 rentals / 2 Stations + 4 rentals / 3 Stations)

Can anyone help me with that?

Thank you very much!

[Stachu · Accepted solution]

this should do the trick

Measure =
VAR RentalsWithStations =
    ADDCOLUMNS (
        Rentals,
        "NrOfStations", CALCULATE (
            COUNT ( Stations[Station_ID] ),
            'Calendar'[Date] <= EARLIER ( Rentals[Date] )
        )
    )
RETURN
    SUMX (
        GROUPBY (
            RentalsWithStations,
            [NrOfStations],
            "NrOfRentals", COUNTX ( CURRENTGROUP (), [Rental_ID] )
        ),
        DIVIDE ( [NrOfRentals], [NrOfStations] )
    )

BTWin your axample Stations has US date format, and Rentals is dd/mm/yyyy, right? Otherwise I cannot reconcile the numbers with your example

[v-chuncz-msft · Accepted solution]

@ValentinBIA,

You may refer to the DAX below.

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

[ValentinBIA]

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.

Station_ID	Opening date
A	1/1/2018
B	1/1/2018
C	2/1/2018
D	4/1/2018
E	4/1/2018
F	5/1/2018

And here is the rentals table:

Rental_ID	Date
1	5/1/2018
2	9/1/2018
3	20/1/2018
4	20/1/2018
5	22/1/2018
6	29/1/2018
7	2/2/2018
8	5/2/2018
9	5/2/2018
10	25/2/2018
11	9/4/2018
12	11/4/2018
13	21/4/2018
14	3/5/2018
15	13/5/2018
16	15/5/2018
17	15/5/2018
18	19/5/2018
19	25/5/2018
20	25/5/2018

What I currently have is measure that gives me the average number of rentals by stations, but considers that there are 6 stations at all times.

Avg_rentals = DIVIDE(COUNT(Rentals[Rental_id]),COUNTX(Stations,Stations[Station_ID))

I use slicers to filter rentals by date (and also by city, but not explained in this example).

For example is if I select date from 1/1/2018 to 3/1/2018 (US format), I want the measure to give me the result:

Average rentals = 4.33 (which is 6 rentals / 2 Stations + 4 rentals / 3 Stations)

Can anyone help me with that?

Thank you very much!

[Stachu · Accepted solution]

this should do the trick

Measure =
VAR RentalsWithStations =
    ADDCOLUMNS (
        Rentals,
        "NrOfStations", CALCULATE (
            COUNT ( Stations[Station_ID] ),
            'Calendar'[Date] <= EARLIER ( Rentals[Date] )
        )
    )
RETURN
    SUMX (
        GROUPBY (
            RentalsWithStations,
            [NrOfStations],
            "NrOfRentals", COUNTX ( CURRENTGROUP (), [Rental_ID] )
        ),
        DIVIDE ( [NrOfRentals], [NrOfStations] )
    )

BTWin your axample Stations has US date format, and Rentals is dd/mm/yyyy, right? Otherwise I cannot reconcile the numbers with your example

[ValentinBIA]

Hello @Stachu, thank you very much for taking the time to try to solve my problem!

You are right, I made a mistake when I wrote the dummy data, but indeed the rentals are dd/mm/yyyy, and stations have US format.

I implemented your measure in my file, but currently it gives me to the total number of rentals, not the average per station.

Do you have any idea of what I could try to change?

Thank you!

Best,

Valentin

[Stachu]

are you using date from Calendar table in the visual and the slicer?
Calendar should be joined with both tables by 1:many reliationship with single direction

[ValentinBIA]

@Stachu, @v-chuncz-msft, thank you very much to both of you!

So, I adjusted the relationships, and both of your solutions work perfectly fine!

However, and I should have be more precise (I did not think it would be an issue), I also filter sometimes by the region or town the stations are located in. And whenever I do so, your measure don't work anymore (as you did not have this info when writting them).

Do you think of a tweak that would allow me to filter by town and region? Or do you need more dummy data to show you exactly what I mean?

Best,

Valentin

[ValentinBIA]

Hey @Stachu and @v-chuncz-msft, any idea on how to adjust the formula? Or should I just make another post?