Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |