Forum Discussion

ValentinBIA's avatar
ValentinBIA
Icon for Resolver I rankResolver I
8 years ago
Solved

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...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    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's avatar
    v-chuncz-msft
    8 years ago

    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's avatar
ValentinBIA
Icon for Resolver I rankResolver I
8 years ago

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!