Forum Discussion

Kitty-SD's avatar
Kitty-SD
Frequent Visitor
2 years ago
Solved

Calculating sum with duplicated rows

I have a table that is in the general structure of the table below - each booking has multiple itinerary lines. My problem is that I need the pax calculation to work, regardless of the filters I apply to the page.

I originally was using the Countrows1 custom column which counted how many times the booking appears, so in this instance the pax would be the sum of pax divided by the countrows (8 / 4 = 2). 

 

Countrows1 = CALCULATE(COUNT(Bookings,BookId),ALLEXCEPT(Bookings,BookId))

Pax1 = SUMX(Bookings, Pax / Countrows1)

 

The problem is that this doesn't work when I need to filter by country. So in this instance, if I filtered to Mozambique, it shows 0.5 pax (2 / 4 = 1.5). 

 

I then tried adding another column, Countrows2, so that it counts the number of rows per booking per country. 

 

Countrows2 = CALCULATE(COUNTROWS(Bookings),FILTER(Bookings,[BookId] = EARLIEST(Bookings[BookId])),FILTER(Bookings,[Country]=EARLIEST(Bookings,[Country])))

Pax2 = SUMX(Bookings, Pax / Countrows2)

 

The problem here is that it works as long as you filter the country to either Mozambique or Zimbabwe, but if you don't apply that filter, the total pax is 4.

 

Is there a way of calculating the pax so that it calculates correctly & duplicates / deduplicates when needed, whether or not I apply a country filter to the report?

 

Thanks!

2 Replies