Fixed level of detail (complicated?)

[Confultant]

Hi,

I'm new to PBI but have used Tableau in the past. What I'm trying to do is to replicate something similar to Tableau's 'FIXED' Level of Detail function. 

I am trying to analyse a large set of transaction data, but the way the raw data is structured is clunky. Essentially, each line represents an item purchased or a payment, and is only tied together by a transaction ID. Data is also kind of 'duplicated' in the sense that there is a 'number of diners' field that is the same across all lines (see below).

What I want to do are the following analyses:

1. Total/average number of diners over time (can't just sum columns because of duplication, as mentioned above)

2. Average number of items sold per transaction

3. Average spend per transaction (can't just sum everything as each ID includes the menu items ordered, and another line for the payment itself, which equals sum of item cost + VAT)

In Tableau, what I would do is to AVERAGE number of diners and FIX it to the transaction ID. Similarly, I'd COUNT number of rows and FIX to the transaction ID. I've kind of done the same in PowerBI by creating new columns to COUNTROWS or SUM at the Transaction ID level, example:

Count of items sold (use for ID-level analysis) =

CALCULATE(countrows('TABLE'),

ALLEXCEPT(TABLE ,TABLE [Transaction ID]), Table [Record type]="Payment")

but this analysis breaks down when I try to analyse by a different measure, e.g., geography.

Example: Imagine I have 10 transactions, transaction 1 has 1 item, 2 has 2 items, 3 has 3... I've 'FIXED' the number of diners to each transaction ID. It's OK if you want to analyse at the ID level, but now if I sum or average across another variable, say, time, the resulting output would no longer be accurate because of the different number of records each transaction has. 

I know this is long and complicated, but any help appreciated!

Store ID	Date	Transaction ID	Record type	Item ID	name	Item qty	Charge	Number of diners
001	1/1/2024	1A	Menu item	265	Item A	1	17.9	4
001	1/1/2024	1A	Menu item	265	Item A	1	28.9	4
001	1/1/2024	1A	Menu item	265	Item A	1	4.5	4
001	1/1/2024	1A	Menu item	265	Item A	1	17.9	4
001	1/1/2024	1A	Menu item	265	Item A	1	6.8	4
001	1/1/2024	1A	Menu item	265	Item A	1	1.72	4
001	1/1/2024	1A	Menu item	265	Item A	1	1.72	4
001	1/1/2024	1A	Menu item	265	Item A	1	3	4
001	1/1/2024	1A	Menu item	265	Item A	2	8	4
001	1/1/2024	1A	Menu item	265	Item A	1	5	4
001	1/1/2024	1A	Menu item	265	Item A	3	18	4
001	1/1/2024	1A	Menu item	265	Item A	2	12	4
001	1/1/2024	1A	Menu item	266	Item B	1	6	4
001	1/1/2024	1A	Payment	999	Credit card	1	144.584	4

[Confultant]

There's a few things I want to do, but take the analyses wishlist, for example:

1. Total/average number of diners over time

2. Average number of items sold per transaction

3. Average spend per transaction

I want to put these into a time series matrix table, e.g., each row is a month, then in Jan/Feb/Mar, things like the sum total of transactions, sum total of items sold, as well as, per receipt (transaction ID), the average spend, average number of items purchased etc.

[lbendlin]

OK, go ahead and implement all this in Power BI, I don't see any showstoppers.  Note that if you are looking for LOD - Power BI does not support automatic bucketing, you need to bring your own buckets. 

If you like further help please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

What is the expected outcome based on your sample data?