Average of values by each category

[HemanthV]

Hi Guys,
How to achieve average of values by each category

Here in the above picture Average measure is displaying total average, but i need to display average for each category i.e,
Expected output:

The result needs be dynamic based on slicer selections
attaching pbix for referrence
https://drive.google.com/file/d/1Z1uXXbRszEO--PT1GAiO6k4HsHhq-OM2/view?usp=sharing

Thanks in advance!

[Vera_33]

Hi @HemanthV 

avg_c = 
VAR CurC = SELECTEDVALUE('sample'[symbol])
RETURN
AVERAGEX(FILTER(ALLSELECTED('sample'),'sample'[symbol]=CurC),'sample'[trade_volumn])

[HemanthV]

Hi @Vera_33 ,
Thanks for the solution, but i need some more help from you. I have placed the dax that you have sent, it is working but here I am using a what if parameter

In the above picture, i have created a measure which indicate "1" if greater% value greater than parameter.
when i try to filter the ones from filterpane. I am getting results like below

I am getting the rows correct but  the average measure is changing. It should not change because, we are seeing average for last 10 days in the first pic, i have not changed the date slicer here. so average should be as mentioned in the first image.

To simplify the requirement the average measure needs to ignore measure filter from filter pane.

Please find the attached pbix
https://drive.google.com/file/d/1Z1uXXbRszEO--PT1GAiO6k4HsHhq-OM2/view?usp=sharing.

Thanks in advance!

[Vera_33 · Accepted solution]

Hi @HemanthV 

It was ALLSELECTED. Slightly changed it, with a connected Date table in the slicer. You can call this in your [greater%]

avg = 
VAR CurC = SELECTEDVALUE('sample'[symbol])
VAR T1 = FILTER(ALL('sample'),'sample'[symbol]=CurC&&'sample'[date] in VALUES('Date'[Date]))
RETURN
AVERAGEX(T1,'sample'[trade_volumn])

[HemanthV]

Hi @Vera_33 ,
Can this measure be optimized. Because when i am filtering only for last 10 days the measure is working fine but when i filter for last 30 days data i am getting error in power bi service.

Can you please tweak the measure and make it run faster. I have tried to optimize it but, i don't know how to do it. 

Please help me on this!

Thanks in advance!

[Vera_33]

Hi @HemanthV 

Try this one, but I am not sure if you have too many items in one visual...

avg = CALCULATE(AVERAGE('sample'[trade_volumn]),ALL('sample'),KEEPFILTERS(VALUES('sample'[symbol])),KEEPFILTERS(VALUES('Date'[Date])))

[HemanthV]

Hi @Vera_33 ,
This worked like a charm, first one took almost 12 seconds for 2 months data, new measure is not even taking 1 second. Thank you so much. Thanks for helping me out!

[HemanthV]

Hi @Vera_33 ,
Thank you so much for helping me out, It worked. Thanks a lot!