DAX based way of using agg daily averages to create a custom formula via a multiselect slicer

[530]

I'm looking at trying to create a custom value from a multiselect slicer but when I try to do things with ALLSELECTED or SELECTEDVALES or VALUES I get weird interactions like yeilding blanks.

The workflow I need:

1. Select two categories in slicer from ~50

2. Get the daily average value of those categories -- needed for the custom calculation below

3. Look at the daily value using those daily average values in the form 1 - (daily avg, cat1/daily avg, cat2) 

4. Plot this custom formula value by each day in a PowerBI visual   

An Example: 

After choosing the category F and P in my slicer my filtered dataset would like this:

Date		Type		Reading
20221026		F		1000
20221026		P		4
20221025		F		1100
20221025		P		5
20221025		F		1200
20221024		P		7
20221024		F		1150
20221023		P		11
20221023		F		1000
20221023		P		12

Then the daily averages for each type would look something like: 

Date		Type		Avg Reading
20221026		F		1000
20221026		P		4
20221025		F		1150
20221025		P		5
20221024		F		1150
20221024		P		7
20221023		F		1000
20221023		P		11.5

The custom table would something like: 

Date		Custom Value
20221026		0.996
20221025		0.995652
20221024		0.993913
20221023		0.9885

And the visual would look something like this: 

Thanks for the help! 

[530]

@v-xiaosun-msft, 

Thanks again for the help. 

To get that custom calculation I would need a calculation that returns for a given day: 1 - (daily avg of first selected value in my slicer)/(daily avg of second selected value in my slicer). The relationship between it and the average column is the day -- the x axis of the visual. So for 2022/10/26: daily avg of F = 1000; daily avg of P = 4; so custom is 1-(4/1000) =  0.996. 

To get the daily averages in a DAX way I did this but I'm not sure if helps me get to the final needed custom calculation: 

test_num_selected =

VAR Table1 = SUMMARIZE(

                'HI <> Semantic Layer',

  'HI <> Semantic Layer'[Day],

                'HI <> Semantic Layer'[semantic-layer.Semantic Layer],

                'HI <> Semantic Layer'[Reading]

            )

VAR AVG_reading = GROUPBY(Table1,'HI <> Semantic Layer'[Day],'HI <> Semantic Layer'[semantic-layer.Semantic Layer], "@AVG_reading", AVERAGEX(CURRENTGROUP(),'HI <> Semantic Layer'[Reading]))

RETURN MAXX(AVG_reading, [@AVG_reading])

[v-xiaosun-msft]

Hi @530 ,

Could you tell me what the definition of your custom calculation is? What is the relationship between it and the "average" column? According to your description, the visual is created by the custom value.

Please provide more description to help you better.

Best Regards,
Community Support Team _ xiaosun

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