Unable to get desired results when trying to slice for multiple accounts

user_14065909 0
Viewed 38

In my sales report, I have run into a problem where sales for some customers are sold under another customer and I need to attribute sales to both to show truly accurate values.

Pictured below are the tables in question. tblTicketDetails is my facts table with the other two being my dimension tables. enter image description here

Here is an example of the accounts in question:

enter image description here

In the above example, all values in [Account #] belong to customer 1 and values in [LookupAccount] belong to others (lets say customer 2 and 3). I typically use [Customer Name] as my value in my Slicer as all accounts for a customer are named the same.

The outcome I have been trying to obtain is when I slice by customer 1, I get all values shown in [Account #] that have Customer 1 as the name but if I slice by customer 2 (who lets say has one account and is 5809), I will get all [Account #] that equal 5809 as well as all [Account #] whos [LookupAccount] is also 5809 (so I would also get 23634, 37765, 67804 and 95511).

The same applies to those who have multiple accounts, so if I slice by customer 3 whos accounts are lets say 14650, 89670 and 47900, I would get those results from [Account #] as well as the accounts where {LookupAccount] matches, which in this case would be 19734, 28199 and 64218.

I have tried changing the relationship between tblCustomers and tblTicketDetails, I have also added in the table LookupAccount as an in-between for the many to many relationship but none of those have actually changed the results I get (or they cause the visuals to go blank).

What am I missing?

powerbi-desktop powerbi
1 Answers

Based on clarification from the author, here is my updated answer.

There are three objects in this model:

  • Sales in tblTicketDetails
  • Customers in tblCustomers
  • A matching table between the two, currently LookupAccount (the table)

Here is a abstract of the set or rules at stakes :

  • A sale has an account number
  • A customer is identified by his account number, let's call it primary account
  • A customer might use another account number in some situation. Let's call it secondary account

The goal is: when filtering on a customer, all sales are return whether they are through his primary account or secondary account.

The maching table LookupaAccount needs a rework for the model to function properly. For the stake of clarification, columns are renamed:

  • Account# → Primary
  • LookupAccount → Secondary

Here is an example:

Primary Secondary
1 2
1 3
2 4

In that example:

  • Client account 1 has sales on accounts: 1, or 2, or 3.
  • Client account 2 has sales on accounts: 2, or 4.

In PowerQuery, we want to do some transformations:

  1. Duplicate Primary column and call it CustomerAccount. This is the foreing key that will links to tblCustomers.
  2. Unpivot columns Primary and Secondary with the value column renamed as ReportingAccount, which will be the foreign key to tblTicketDetails

The end result should look like this:

CustomerAccount Attribute ReportingAccount
1 Primary 1
1 Secondary 2
1 Primary 1
1 Secondary 3
2 Primary 2
2 Secondary 4

Then, you modify the relationships in your model:

  • tblTicketDetails is connected to LookupAccount (table) from field Account# to ReportingAccount
  • LookupAccount (table) is connected to tblCustomers from field CustomerAccount to Account#

Normaly this model should flow better.

Now, you can create measure to calculate de amount of sales per customer :

SalesperCustomer =
CALCULATE(
    SUM(tblTicketDetails[Total])
)

Or, for example just the sales through primary account

SalesperCustomer_primary =
CALCULATE(
    SUM(tblTicketDetails[Total])
    LookupAccount[Attribute] = 'Primary'
)
user_58872670
Related