Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bitraveldepart
Frequent Visitor

Changeable Rate of Sale - Cumulative Booked Pax/ Cumulative Tour Capacity by Slicer

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:

TourYearTourDateBookedDateTourIDTourNamePaxWeeksPriorTourCapacityTourMonth
201716/10/201726/05/201723425Italy2211010
201716/10/201701/06/201723425Italy2221010
201716/10/201707/06/201723425Italy3231010
201716/10/201715/06/201723425Italy1241010
201718/10/201716/06/201723472Japan224810
201718/10/201702/08/201723472Japan231810
201718/10/201703/08/201723472Japan231810
201809/05/201812/02/201887965India3785
201809/05/201819/08/201787965India1-1985
201809/05/201819/03/201887965India21285
201811/05/201812/02/201854254China2765
201811/05/201819/08/201754254China3-1965
201811/05/201819/03/201854254China11265
201807/09/201828/09/201715621Iceland2-1369
201807/09/201824/04/201815621Iceland21769
201807/09/201812/06/201815621Iceland22469

 

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

TourYearTourDateBookedDateTourIDTourNamePaxWeeksPriorcunmulativeTourCapacity by TourNameTourMonthCumulativeSoldcumulative Pax/capacity by tour
201716/10/201726/05/201723425Italy2211010220%
201716/10/201701/06/201723425Italy2221010440%
201716/10/201707/06/201723425Italy3231010770%
201716/10/201715/06/201723425Italy1241010880%
201718/10/201716/06/201723472Japan224810225%
201718/10/201702/08/201723472Japan231810450%
201718/10/201703/08/201723472Japan231810675%
201809/05/201812/02/201887965India3785338%
201809/05/201819/08/201787965India1-1985450%
201809/05/201819/03/201887965India21285675%
201811/05/201812/02/201854254China2765233%
201811/05/201819/08/201754254China3-1965583%
201811/05/201819/03/201854254China112656100%
201807/09/201828/09/201715621Iceland2-1369233%
201807/09/201824/04/201815621Iceland21769467%
201807/09/201812/06/201815621Iceland224696100%

 

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

TourYearTourDateBookedDateTourIDTourNamePaxWeeksPriorcumulativeTourCapacity by TourMonthTourMonthCumulative soldcumulative Pax/capacity by year
201716/10/201726/05/201723425Italy2211810211%
201716/10/201701/06/201723425Italy2221810422%
201716/10/201707/06/201723425Italy3231810739%
201716/10/201715/06/201723425Italy1241810844%
201718/10/201716/06/201723472Japan22418101056%
201718/10/201702/08/201723472Japan23118101267%
201718/10/201703/08/201723472Japan23118101478%
201809/05/201812/02/201887965India37145321%
201809/05/201819/08/201787965India1-19145429%
201809/05/201819/03/201887965India212145643%
201811/05/201812/02/201854254China27145857%
201811/05/201819/08/201754254China3-191451179%
201811/05/201819/03/201854254China1121451286%
201807/09/201828/09/201715621Iceland2-1369233%
201807/09/201824/04/201815621Iceland21769467%
201807/09/201812/06/201815621Iceland224696100%

 




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

 

1 ACCEPTED SOLUTION

Hi bitraveldepart,

 

Can this work?

Percentage =
CALCULATE (
    SUM ( Table1[Pax] ) / SUM ( Table1[TourCapacity] ),
    ALLSELECTED ( Table1 )
)

Regards,

Jimmy Tao

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

Hi bitraveldepart,

 

You can create a line chart to compare values in different year like below:

Capture.PNG 

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))

Capture2.PNG 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.