topic Re: Creating Average measures for table in DAX Commands and Tips

Creating Average measures for table

mbowler — Tue, 09 Jul 2024 18:00:59 GMT

I have a table of ticket sales for different events, each row is a seat. If the seat is still available the "Price" column is empty. I have created a couple of measures that give me the number of ticket sales and the total renevue that will then repond to the filter context I am currently using. However I wish to create an average #sales and average revenue per event for the context.

My measures are:

Total Sales = COUNT(AllSeatsData[Price]) Total Revenue = SUM(AllSeatsData[Price])

Here is some samples of my table:

Hence if my filter context has only the two above events in it, I am looking for measure that gives me the average of SUM([Price]) and one that gives me the average of COUNT([Price]). E.g. if the data above were the only rows in the table for these events, the average of #sales should be 9 (event 39001 has 6 sales, event 45201 has 12, average of 6 & 12 is 9).

I'm sure this is quite easy but I can't get my head around the maniputation of the context.

TIA.

Re: Creating Average measures for table

AlexisOlson — Tue, 09 Jul 2024 19:47:47 GMT

First aggregate at the level you need for your business logic, then use AVERAGEX. If event is the granularity you want to average at, try this:

Event Avg Count = VAR __Events = SUMMARIZE ( AllSeatsData, AllSeatsData[Event Instance ID], "@Count", COUNTA ( AllSeatsData[Price] ) ) RETURN AVERAGEX ( __Events, [@Count] )

Re: Creating Average measures for table

mbowler — Mon, 15 Jul 2024 07:45:35 GMT

Brilliant, that works great. Thanks AlexisOlson !