Forum Discussion
Incremental Values/ group by Relative week
Hi,
I'd like to see the incremental Pax number from the beginning of the year till now and compare the rate of target.
So I have my Pax number for each booking record with date, relative week, month and year.
Original table as follow:
| Tour_ID | Book Date | Month | Relative week | Pax |
| 1001 | 01/01/2017 | 1 | 1 | 3 |
| 1002 | 22/01/2017 | 1 | 4 | 1 |
| 1003 | 03/02/2017 | 2 | 5 | 2 |
| 1004 | 04/02/2017 | 2 | 5 | 5 |
then sum up the incremental Pax number by Relative week:
| Relative week | total Pax |
| 1 | 3 |
| 4 | 4 |
| 5 | 11 |
and come up with something like
The dataset is more complex but this is basically the idea. Anyone knows how to transfer the original table (by Tour_ID) to another table (by Relative week) and sum up the incremental value as well?
Thanks!
Create a summary table, grouping by week and SUM the passengers
sumtab = SUMMARIZE(TourPassengers, TourPassengers[RelativeWeek], "total Pax", SUM(TourPassengers[Pax]))Then add a calculated column for cumulative totals
CumulPax = CALCULATE ( SUM(sumtab[total Pax]), FILTER ( ALL ( sumtab ), sumtab[RelativeWeek] <= EARLIER ( sumtab[RelativeWeek])))
5 Replies
- Greg_Deckler
Community Champion
You could do a sort and then an Index in Power Query. Or maybe my Sequential Quick Measure will work:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231
- HotChilli
Create a summary table, grouping by week and SUM the passengers
sumtab = SUMMARIZE(TourPassengers, TourPassengers[RelativeWeek], "total Pax", SUM(TourPassengers[Pax]))Then add a calculated column for cumulative totals
CumulPax = CALCULATE ( SUM(sumtab[total Pax]), FILTER ( ALL ( sumtab ), sumtab[RelativeWeek] <= EARLIER ( sumtab[RelativeWeek])))Hi,
My [Relative Week] is a column not meature. Therefore it doesn't recognize the column from the table.
Does it make sense?
Thanks,
- HotChilli
I think that's just a syntax error. You'll need to change the code i submitted so that it works with your tables/columns e.g my sample table is 'RelativeWeek' but yours should have a space in there
