Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
I'm new to PowerBI but I think this is a fairly big project so hopefully this is challenging enough for you guys coz I've been pulling out my hair from this.
I want to get my rate of sale (cumulative pax sold/cumulative tour capacity) according to the filter i select from Slicers. i.e: by TourName, TourMonth etc. And I want to see 2 lines (2017, 2018) in one chart with WeekPrior being Axis
So this is my original data structure:
TourYear | TourDate | BookedDate | TourID | TourName | Pax | WeeksPrior | TourCapacity | TourMonth |
2017 | 16/10/2017 | 26/05/2017 | 23425 | Italy | 2 | 21 | 10 | 10 |
2017 | 16/10/2017 | 01/06/2017 | 23425 | Italy | 2 | 22 | 10 | 10 |
2017 | 16/10/2017 | 07/06/2017 | 23425 | Italy | 3 | 23 | 10 | 10 |
2017 | 16/10/2017 | 15/06/2017 | 23425 | Italy | 1 | 24 | 10 | 10 |
2017 | 18/10/2017 | 16/06/2017 | 23472 | Japan | 2 | 24 | 8 | 10 |
2017 | 18/10/2017 | 02/08/2017 | 23472 | Japan | 2 | 31 | 8 | 10 |
2017 | 18/10/2017 | 03/08/2017 | 23472 | Japan | 2 | 31 | 8 | 10 |
2018 | 09/05/2018 | 12/02/2018 | 87965 | India | 3 | 7 | 8 | 5 |
2018 | 09/05/2018 | 19/08/2017 | 87965 | India | 1 | -19 | 8 | 5 |
2018 | 09/05/2018 | 19/03/2018 | 87965 | India | 2 | 12 | 8 | 5 |
2018 | 11/05/2018 | 12/02/2018 | 54254 | China | 2 | 7 | 6 | 5 |
2018 | 11/05/2018 | 19/08/2017 | 54254 | China | 3 | -19 | 6 | 5 |
2018 | 11/05/2018 | 19/03/2018 | 54254 | China | 1 | 12 | 6 | 5 |
2018 | 07/09/2018 | 28/09/2017 | 15621 | Iceland | 2 | -13 | 6 | 9 |
2018 | 07/09/2018 | 24/04/2018 | 15621 | Iceland | 2 | 17 | 6 | 9 |
2018 | 07/09/2018 | 12/06/2018 | 15621 | Iceland | 2 | 24 | 6 | 9 |
If I want to add the Cumulative Pax and see how well I sell each tour by comparing my capacity and filtered by TourName so i get this
TourYear | TourDate | BookedDate | TourID | TourName | Pax | WeeksPrior | cunmulativeTourCapacity by TourName | TourMonth | CumulativeSold | cumulative Pax/capacity by tour |
2017 | 16/10/2017 | 26/05/2017 | 23425 | Italy | 2 | 21 | 10 | 10 | 2 | 20% |
2017 | 16/10/2017 | 01/06/2017 | 23425 | Italy | 2 | 22 | 10 | 10 | 4 | 40% |
2017 | 16/10/2017 | 07/06/2017 | 23425 | Italy | 3 | 23 | 10 | 10 | 7 | 70% |
2017 | 16/10/2017 | 15/06/2017 | 23425 | Italy | 1 | 24 | 10 | 10 | 8 | 80% |
2017 | 18/10/2017 | 16/06/2017 | 23472 | Japan | 2 | 24 | 8 | 10 | 2 | 25% |
2017 | 18/10/2017 | 02/08/2017 | 23472 | Japan | 2 | 31 | 8 | 10 | 4 | 50% |
2017 | 18/10/2017 | 03/08/2017 | 23472 | Japan | 2 | 31 | 8 | 10 | 6 | 75% |
2018 | 09/05/2018 | 12/02/2018 | 87965 | India | 3 | 7 | 8 | 5 | 3 | 38% |
2018 | 09/05/2018 | 19/08/2017 | 87965 | India | 1 | -19 | 8 | 5 | 4 | 50% |
2018 | 09/05/2018 | 19/03/2018 | 87965 | India | 2 | 12 | 8 | 5 | 6 | 75% |
2018 | 11/05/2018 | 12/02/2018 | 54254 | China | 2 | 7 | 6 | 5 | 2 | 33% |
2018 | 11/05/2018 | 19/08/2017 | 54254 | China | 3 | -19 | 6 | 5 | 5 | 83% |
2018 | 11/05/2018 | 19/03/2018 | 54254 | China | 1 | 12 | 6 | 5 | 6 | 100% |
2018 | 07/09/2018 | 28/09/2017 | 15621 | Iceland | 2 | -13 | 6 | 9 | 2 | 33% |
2018 | 07/09/2018 | 24/04/2018 | 15621 | Iceland | 2 | 17 | 6 | 9 | 4 | 67% |
2018 | 07/09/2018 | 12/06/2018 | 15621 | Iceland | 2 | 24 | 6 | 9 | 6 | 100% |
Now, if i want to see my rate of sale by Tour Month, there are multipuls tours happening in the same month so my cumulative tour capacity by month will be the sum capacity of tours departure that month. it will be something like this
TourYear | TourDate | BookedDate | TourID | TourName | Pax | WeeksPrior | cumulativeTourCapacity by TourMonth | TourMonth | Cumulative sold | cumulative Pax/capacity by year |
2017 | 16/10/2017 | 26/05/2017 | 23425 | Italy | 2 | 21 | 18 | 10 | 2 | 11% |
2017 | 16/10/2017 | 01/06/2017 | 23425 | Italy | 2 | 22 | 18 | 10 | 4 | 22% |
2017 | 16/10/2017 | 07/06/2017 | 23425 | Italy | 3 | 23 | 18 | 10 | 7 | 39% |
2017 | 16/10/2017 | 15/06/2017 | 23425 | Italy | 1 | 24 | 18 | 10 | 8 | 44% |
2017 | 18/10/2017 | 16/06/2017 | 23472 | Japan | 2 | 24 | 18 | 10 | 10 | 56% |
2017 | 18/10/2017 | 02/08/2017 | 23472 | Japan | 2 | 31 | 18 | 10 | 12 | 67% |
2017 | 18/10/2017 | 03/08/2017 | 23472 | Japan | 2 | 31 | 18 | 10 | 14 | 78% |
2018 | 09/05/2018 | 12/02/2018 | 87965 | India | 3 | 7 | 14 | 5 | 3 | 21% |
2018 | 09/05/2018 | 19/08/2017 | 87965 | India | 1 | -19 | 14 | 5 | 4 | 29% |
2018 | 09/05/2018 | 19/03/2018 | 87965 | India | 2 | 12 | 14 | 5 | 6 | 43% |
2018 | 11/05/2018 | 12/02/2018 | 54254 | China | 2 | 7 | 14 | 5 | 8 | 57% |
2018 | 11/05/2018 | 19/08/2017 | 54254 | China | 3 | -19 | 14 | 5 | 11 | 79% |
2018 | 11/05/2018 | 19/03/2018 | 54254 | China | 1 | 12 | 14 | 5 | 12 | 86% |
2018 | 07/09/2018 | 28/09/2017 | 15621 | Iceland | 2 | -13 | 6 | 9 | 2 | 33% |
2018 | 07/09/2018 | 24/04/2018 | 15621 | Iceland | 2 | 17 | 6 | 9 | 4 | 67% |
2018 | 07/09/2018 | 12/06/2018 | 15621 | Iceland | 2 | 24 | 6 | 9 | 6 | 100% |
Ultimately the rate of sale should always be growing and getting closer to 100% as customers keep booking the tours.
So my questions are:
1. There are 2 dateframes, Booked Date and Tour Date, how can i keep them in one chart with both 2017 and 2018 rate of sale as comparison.
2. How can I create a measure where rate of sale is changeable, in a way the cumulative sold pax and cumulative tour capacity can be grouped based on the slicers.
3. I can create a separate table with tour capactiy info if this helps with the cumulative measure calculation.
Thanks for reading this far! There are multipul quesions/ points in this project and potientially more that i haven't spotted. but hopefully i've illustrate the idea fairly clearly.
Thanks!!
Solved! Go to Solution.
Hi bitraveldepart,
Can this work?
Percentage = CALCULATE ( SUM ( Table1[Pax] ) / SUM ( Table1[TourCapacity] ), ALLSELECTED ( Table1 ) )
Regards,
Jimmy Tao
Hi bitraveldepart,
You can create a line chart to compare values in different year like below:
In addtion, you can create two measures based on the selected values in slicer to achieve cumulative value:
Cumulative of Pax = CALCULATE(SUM(Table1[Pax]), ALLSELECTED(Table1)) Cumulative of TourCapacity = CALCULATE(SUM(Table1[TourCapacity]), ALLSELECTED(Table1))
Regards,
Jimmy Tao
Hi @v-yuta-msft,
Thank you. I can also get the cumulative Booked Pax and Tour Capacity separately.
The difficulty is to have the percentage (cumulative Booked Pax / cumulative Tour Capacity) changeable based on the slicer.
Is it doable?
Thank you!
Hi bitraveldepart,
Have you solved your issue by now? If you have, could you please kindly mark my answer?
Regards,
Jimmy Tao
Hi bitraveldepart,
Can this work?
Percentage = CALCULATE ( SUM ( Table1[Pax] ) / SUM ( Table1[TourCapacity] ), ALLSELECTED ( Table1 ) )
Regards,
Jimmy Tao
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |