Average After Slicer Input

[PiyushBQ]

Hi,

I have this dataset

Sector	Item	Value	Average (calculate)	Rank (calculate)
Gold	E	89	66.6	1
Gold	C	82	66.6	2
Gold	D	69	66.6	3
Gold	B	51	66.6	4
Gold	A	42	66.6	5
Silver	F	19955	15043.2	1
Silver	H	16487	15043.2	2
Silver	G	14927	15043.2	3
Silver	J	12103	15043.2	4
Silver	I	11744	15043.2	5

The way this works is, the user selects "Silver" or "Gold" from the slicer and the items get ranked dynamically. Also, the average for that particular product category, gold or silver, should reflect in a different column.

I was able to calculate the rank but need guidance on calculating the average.

How do I calculate the average? 

Thank you!
Piyush

[parry2k]

@PiyushBQ wrap SUM in CALCULATE

Avg  = AVERAGEX ( Table, CALCULATE ( SUM ( Table[Column] ) ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[PiyushBQ]

Thank you for taking the time out @parry2k .

I tried doing it but it didn't work. Please check the screenshot.

[parry2k]

@PiyushBQ are you adding this as a measure or column?

[PiyushBQ]

Hi @parry2k I had previously written it as a measure. 

But adding it as a column was fraught with problems too. Here's a screenshot of the error when added as a column.

[V-lianl-msft · Accepted solution]

Hi @PiyushBQ ,

Try create measure like this:

Measure = CALCULATE(AVERAGE('Table'[Value]),ALLSELECTED('Table'))

Or calculated column:

Column = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Sector]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.