Relationships between Tables

[ElliotP]

Morning,

I'm attempting to visualise the Net Sales per group size in my venue. I've been able to work out the group size and give them an ID (1 for one person, 2 for a group of 2, etc). At the moment when i attempt to visualise this it appears as (https://gyazo.com/f8bc3deeb9c6f74af5c3345b495f2fc9). Net sales is being summed and is the same for each value of group size id. To me this feels like an issue with the relationships between the tables as the total's are equalling the day (from the date table which is linked to the data table), but not responding to the group size by ID.

Here is a look at my relationships (https://gyazo.com/e8153707f2611c16ee377753dcff9a2f). It is a bit messy. I had to create multiple tables to be able to work through all of the conditions in order to arrive at the final Group Size ID numbers (in the group size table). I can share a pbix if somebody would like to have a try, but I'm a little out of ideas as the tables SUMMARIZE off each other in order to deal with all of the conditions before finally arriving at the Group Size table where it is all amalgamated and nice.

Feelings?

[dkay84_PowerBI]

Are you using a custom measure to calculate the values or relying on the aggregation within the table visual based on the relationships?

Also, this may be off but based on your description, if you have a group size ID, you can group your data by ID and by Date, and sum the sales values, all through the query editor.  I would start by referencing (or duplicating) your table where the sales data comes from.  Then, in this new table, apply your logic within the query editor "Add Custom Column" to get a group ID.  Then use the Group By functionality in the UI and add two 'levels' of grouping (ID and Date) and set the aggregation to "Sum" and the column to your Net Sales column.  Then, just link this new grouped table with your others (i.e. date dimension).

[ElliotP]

Thanks for the response; my custom column ability to terrible. I feel IF functions are certainly the way to go; the two conditions are;

1. The first being, if there are multiple different unique transaction IDs within a ten minute rolling period have the same value such as “25in” appear in the Notes column of the ‘itemdetailsdogfood$’ table then these orders are classified as being a part of a group of people. So if for example there were 3 Unqiue transaction IDs between 8.21am and 8.27am with the same Note “25 In” for example then that is a group of three people.

2.The next part is if on a Unique Transaction ID there is more than one of any item category in the [Section2] category then the subsequent numbers represent an additional person. So each group begins with one person. If for example there are two “barista” values which appear within one Unique Transaction ID then that represents a group of two people.

The second condition I feel is a lot easier, the first is more complicated.