cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValentinBIA Regular Visitor
Regular Visitor

Filter Count

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_IDOpening date
A1/1/2018
B1/1/2018
C2/1/2018
D4/1/2018
E4/1/2018
F5/1/2018

 

And here is the rentals table:

Rental_IDDate
15/1/2018
29/1/2018
320/1/2018
420/1/2018
522/1/2018
629/1/2018
72/2/2018
85/2/2018
95/2/2018
1025/2/2018
119/4/2018
1211/4/2018
1321/4/2018
143/5/2018
1513/5/2018
1615/5/2018
1715/5/2018
1819/5/2018
1925/5/2018
2025/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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Filter Count

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

Community Support Team
Community Support Team

Re: Filter Count

@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] )
                )
            )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
ValentinBIA Regular Visitor
Regular Visitor

Filter Count

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_IDOpening date
A1/1/2018
B1/1/2018
C2/1/2018
D4/1/2018
E4/1/2018
F5/1/2018

 

And here is the rentals table:

Rental_IDDate
15/1/2018
29/1/2018
320/1/2018
420/1/2018
522/1/2018
629/1/2018
72/2/2018
85/2/2018
95/2/2018
1025/2/2018
119/4/2018
1211/4/2018
1321/4/2018
143/5/2018
1513/5/2018
1615/5/2018
1715/5/2018
1819/5/2018
1925/5/2018
2025/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!

Super User
Super User

Re: Filter Count

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 Regular Visitor
Regular Visitor

Re: Filter Count

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

Community Support Team
Community Support Team

Re: Filter Count

@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] )
                )
            )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Filter Count

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 Regular Visitor
Regular Visitor

Re: Filter Count

@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 Regular Visitor
Regular Visitor

Re: Filter Count

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