cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValentinBIA
Resolver I
Resolver I

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
Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

v-chuncz-msft
Community Support
Community Support

@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.

View solution in original post

7 REPLIES 7
v-chuncz-msft
Community Support
Community Support

@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.

View solution in original post

ValentinBIA
Resolver I
Resolver I

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!

Stachu
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

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
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.