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

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!