Accurate Customer Count from Transaction Detail Table

[tachydidaxy]

Is there a trick to getting an accurate customer count from a transaction detail table? The detail table has one line for each line item on a given check. This is in a restaurant/bar and we need to tally the number of "covers" or "guests" per check. 

My issue is that, if there are lets say 10 items purchased on the check, the customer count is repeated 10 times. So even though there were only two customers on the check when I sum the customer count column I'm getting 20 instead of only two.

I've included a screen grab below for reference. As you can see in the example there were only 3 guests for the check; however summing the guest count column gives me 18 since there were 6 line items.

Here's the cover measure:

Detailed Covers:=CALCULATE (
    SUM ( DetailedChecks[covers] ),
    USERELATIONSHIP ( DetailedChecks[bill_to_member_id], 'Member'[member_id] ),
    FILTER ( 'Type', 'Type'[Type Banding] <> "N/A" )
)

What's the best way to handle this? Any suggestions are welcome.

[v-shex-msft]

Hi @tachydidaxy,

Did you have any other columns which can used to recognize unique id? I'd like to suggest you calculate on that column instead the guest_id.

Regardsm

Xiaoxin sheng

[tachydidaxy]

---

@v-shex-msft wrote:

Hi @tachydidaxy,

Did you have any other columns which can used to recognize unique id? I'd like to suggest you calculate on that column instead the guest_id.

Regardsm

Xiaoxin sheng

---

I do have other columns available, but I'm afraid I'm not understanding what you're suggesting. Can you clarify please?

[popov]

Could you share a sample file which can reproduce the issue?

[tachydidaxy]

---

@popov wrote:
Could you share a sample file which can reproduce the issue?

---

Here's a link, any help is much appreciated:

https://ufile.io/giqzr

[popov · Accepted solution]

Hello, try this formula
Detailed Covers :=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'DetailedChecks'; 'DetailedChecks'[check_id] );
        "CoversPerCheck"; CALCULATE (
            MAX ( 'DetailedChecks'[covers] );
            USERELATIONSHIP ( DetailedChecks[bill_to_member_id]; 'Member'[member_id] )
        )
    );
    [CoversPerCheck]
)

[tachydidaxy]

---

@popov wrote:

Hello, try this formula
Detailed Covers :=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'DetailedChecks'; 'DetailedChecks'[check_id] );
        "CoversPerCheck"; CALCULATE (
            MAX ( 'DetailedChecks'[covers] );
            USERELATIONSHIP ( DetailedChecks[bill_to_member_id]; 'Member'[member_id] )
        )
    );
    [CoversPerCheck]
)

---

Awesome! Works perfectly!

So basically, this is me not completely understanding how to work with differing levels of granularity and not understanding that I have to perform some sort of aggregation on the transaction table in order to get the correct/expected results.

My problem was that I thought the "aggregation" I was initally performing by simply averaging the cover_count when iterating through all the check_ids was sufficient.

As you can see I'm very early in my BI journey; thank you again for your time, I do sincerely appreciate it.

[Greg_Deckler]

Well, you could use AVERAGE perhaps?

[tachydidaxy]

---

@smoupre wrote:

Well, you could use AVERAGE perhaps?

---

@Greg_Deckler thanks, that is an embarrassingly obvious solution and appears to work well, but only on the individual check level (see measure and screen grab below for reference):

Detailed Covers =
CALCULATE (
    SUMX ( VALUES ( DetailedChecks[check_id] ), AVERAGE ( DetailedChecks[covers] ) ),
    USERELATIONSHIP ( DetailedChecks[bill_to_member_id], 'Member'[member_id] )
)

As you can see for the individual checks the measure works swimingly well and gives the correct guest-count/cover-count. However, we are also slicing by restuarant location (i.e. Grill) and we'll also be slicing by purchase-type (i.e. food, drink, etc.). For example check #690664 above shows a value of eleven (11) for the "Detailed Covers" measure, which when we check the back end data in PowerPivot filtered to only that check_id shows is correct (see screen-grab below):

When looking at the location "Grill" however in my inital pivot, you'll notice in the forumla bar that the value is a decimal number (15049.8903160046) . I don't understand this result.

I thought I was having the calculate forumla iterate over the distinct values of the check_id, averaging the guest count for each check_id (which we already know works correcly at the individual check level) and then simply summing those values over all the check_ids for that location. So I don't understand why I'm getting a value like (15049.8903160046, which is rounded to 15,050) for the "Grill" "Detailed Covers" measure as shown in the forumla bar of my screen-grab.

The actual Detailed Covers total for the Grill location in Oct shuld be 10,527 as proven by simply selecting all the check_ids for that month and location and allowing Excel to do the auto-sum (see screen grab below with highlighted auto-sum).