Hi- I think this is a really easy one and i am being a numpty but would appreciate some urgent help please.
I’ve attached a PBIX with an example to help show the issue.
Scenario
In the attached scenario the flow is
pubs to customers
customers to pints drunk with a side table of brand
By Brand type, i would like a count of the number of pubs the customers have drunk in (A customer can only drink in one pub). The issue i have been having with the real example was that i could not get the total to be correct of all the lines added together.
I also want to do a count of the number of pubs in e.g england but that should be answered by the first point.
Thanks and really not sure why i am not getting my head round this?
Using Stella (Brand A) as an example. Customers 10 & 11 drank pints of stella. Customers 10 & 11 are linked to pubs 1&2. Therefore the count of pubs associated with Stella should be 2. And the count of pubs associated with Stella in Scotland should be 1.
Also worth noting that the total does not agree which is another issue i was having.
Well, the solution was within the query which you had asked - “A customer can only drink in one pub”. And therefore, each customer is assigned unique Pub ID against that particular Customer ID but way the data model was designed or the relationship was created didn’t yielded the appropriate results.
In your file, you’ve created a relationship between the “Customers” and the “Pubs” table whereas the “Pints Drunk” (Fact Table) already contained the information of Customer ID.
Now, since your “Pints Drink” table contained the “Customer ID” information and “Customers” table contained the “Customer ID” as well as “Pub ID” information all you had to do is using the “Merge Queries” option inisde the Power Query add a column of “Pub ID” into the “Pints Drink” table. And now, rather than having a relationship between the “Customers” and the “Pubs” table you just need to create a direct relationship between the “Pints Drink” and “Pubs” table. Below is the screenshot of the status of the new relationship provided for the reference -
Now, once the relationship problem was resolved all you’ve to do is revise your “Number of Pubs” measure because as of now, your measure refers to the “Dimension Table”. Below is the revised measure provided for the reference -
Number of pubs =
DISTINCTCOUNT('Pints Drunk'[Pub ID] )
And now, you’ll be able to see the results based on the scenario’s that you had specified.
Scenario 1:The count of pubs associated with Stella should be 2.
Scenario - 2:The count of pubs associated with Stella in Scotland should be 1.
I’m also attaching the working of the PBIX file for the reference purposes.
And with regards to the “Totalling Issues” one of our expert had already created a posted onto our forum which specifically addresses this type of issue. It already contains all the video links as well as different types of scenarios. Below is the link of that post provided for the reference as well.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Hi @Hitman, we’ve noticed that no response has been received from you since October 18.
We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
