Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to create a table that has a dimension of 'Event Date' and 10 expressions that are all similarly structured.
My end goal is to have a value that is the price per ticket paid, based on the total sale divided by the total quantity sold.
My initial expression is: =NUM(avg({$<PRICESCALE={"200 Club End"}>}[TOTALSALE]), '$#,###.') which is giving me the average price of the 'TOTALSALE' when the 'PRICESCALE' is 200 Club End. However, I need to introduce the following count(seatnumber) to divide by the initial expression to result in the average price paid.
I guess, you need the avg Price / ticket paid for respective clubs like 200 Club etc.
if it's set analysis you'd like to go with then, similar to the above comment (but small change):
=Sum({<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({<PRICESCALE={"200 Club End"}>}seatnumber)
Seems like you need this (not 100% sure and may be some sample data might help explain)...
=Sum({$<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count(seatnumber)
I guess, you need the avg Price / ticket paid for respective clubs like 200 Club etc.
if it's set analysis you'd like to go with then, similar to the above comment (but small change):
=Sum({<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({<PRICESCALE={"200 Club End"}>}seatnumber)
This seems to make the number too low.
I have no idea what you are getting and what you want... may be it's time to elaborate
=Sum({$<PRICESCALE={"200 Club End"}>}[TOTALSALE])/Count({$<PRICESCALE={"200 Club End"}>}seatnumber)
I think this may work, but still getting results outside of what I would expect. To test this, Can I perform a set expression with no aggregation function? For example, ={$<PRICESCALE={"300 Level V"}>}[TOTALSALE]), '#,###.')
Not really! As far as I know, Set analysis can only be used with aggregation functions.
I'd approach this problem by
1. Unit testing of separate expressions like checking the individual expressions in a text box. eg. =Sum({$<PRICESCALE={"200 Club End"}>}[TOTALSALE])
OR
2. Using those expressions in table charts to see if any missing values or incompatible conversion errors.
Good luck!