Incorrect Total

[justivan]

Hello, 

I'm stuck on a DAX problem in Power BI and could use some help. I have a table with reservation data, and I'm trying to count the number of unique guests for each booking reference while making sure to only count each guest once, even if they booked multiple hotels.

For example, if I normally do SUMX(Reservation, Adult + Child). That would have returned 4 but the correct result should be 2 only.

RefID	ResID	HotelKey	OperatorKey	Booking Ref	Lead Guest	Adult	Child
2193049	1871763	10000255	16	4688459	Lead Guest225244	2	0
2193048	1871763	10000771	16	4688459	Lead Guest225244	2	0

When trying it in DAX studio with specific rows that falls in the criteria, I seem to get the correct numbers.

Here's the DAX expression I've tried, but it's giving me unexpected results:

Pax = 
VAR _Reservation =
    SUMMARIZE (
        FILTER ( 'Reservation', 'Reservation'[Status] <> "Can" ),
        'Reservation'[OperatorKey],
        'Reservation'[Booking Ref],
        'Reservation'[Lead Guest],
        'Reservation'[Adult],
        'Reservation'[Child]
    )
RETURN
    SUMX ( _Reservation, 'Reservation'[Adult] + 'Reservation'[Child] )

Incorrect result:

The correct total should be 553,536

Here's the PBIX file

https://1drv.ms/u/s!Aj0trVkg_AXisVE0uWATzj22I1-f?e=G3hvPr 

[Wilson_]

Hi Justivan,

I'll defer to you on whether it's legitimate or not in your data, but this must mean some OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations appear in multiple years.

If you add Calendar Year to your _Reservation variable, the calculation works as you expect:

Pax = 
VAR _Reservation =
    SUMMARIZE (
        FILTER ( 'Reservation', 'Reservation'[Status] <> "Can" ),
        'Date'[Calendar Year],
        'Reservation'[OperatorKey],
        'Reservation'[Booking Ref],
        'Reservation'[Lead Guest],
        'Reservation'[Adult],
        'Reservation'[Child]
    )
VAR Result =
    SUMX ( _Reservation, 'Reservation'[Adult] + 'Reservation'[Child] )

RETURN Result

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[justivan]

Hi Wilson,

Sorry for the late reply as I was in a holiday. You are right and that's going to be a problem. Our business requires that it only consider it once based on the first day of the 'In Date'. So for example, with  rows like this, pax should be counted only in 30/12/2023. Any idea how this should be done?

RefID

ResID

HotelKey

OperatorKey

Booking Ref

Lead Guest

In Date

Out Date

Room Type

Days

Meal

Adult

Child

188477

148022

10001072

3

LVWBP

Lead Guest95490

30/12/2023

09/01/2024

Superior Room - Ocean View

10

BB

2

0

188895

148022

10001071

3

LVWBP

Lead Guest95490

05/01/2024

09/01/2024

Superior Room

4

BB

2

0

[Wilson_ · Accepted solution]

Justivan,

Here's a link to my updated pbix.

For this calculation only, it sounds like you need to filter out all the OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations except for the earliest In Date. I added a T/F calculated column to your Reservations table to note whether it is the earliest In Date for that combination, then included that flag in your Pax measure.

The numbers do change a bit, but the visual totals the way you would expect now that the cross-year bookings issue has been resolved. (I only validated the change in the 2025 number, so you may want to validate more thoroughly.)

Hopefully this is the solution you were looking for. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.