Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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] ) ) ) ) ) )
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] ) ) ) ) ) )
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!
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
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
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
@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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |