Filter that will Select the Closest Related Predicted Value

[aar0n]

Hi Guys,

I've Created a Graph in power bi that has the values on the y-axis, and the month number on the x-axis,  something that looks similar to the below sample chart.

Basically, what I need to do, is to find the "Prediction Grade" that most closely resembles the "Actual Value", and have the visual select that Predicted Grade, when I select a Name. 

For example, when I Select "Name 111", the visual will automatically Select "Prediction Grade B", since the average values throughout their tables is the most similar.

(Average of Months 1-12 of "Prediction Grade B" is 19, and Average of Months 1-6 of "Name 111" is 20.8 - therefore those are the best matches..

Average of Months 1-12 of "Prediction Grade A" is 11.7, and Average of Months 1-6 of "Name 222" is 10.33 - therefore those are the best matches )







A sample of my two tables is below.

Predicted Table

Prediction Grade	Month	Predicted Value
A	1	8
A	2	9
A	3	15
A	4	13
A	5	9
A	6	16
A	7	13
A	8	8
A	9	14
A	10	12
A	11	8
A	12	15
B	1	21
B	2	28
B	3	13
B	4	10
B	5	20
B	6	30
B	7	28
B	8	24
B	9	20
B	10	13
B	11	16
B	12	5

Actual Table

Name	Month	Actual Value
111	1	22
111	2	29
111	3	15
111	4	5
111	5	20
111	6	34
222	1	10
222	2	9
222	3	11
222	4	12
222	5	15
222	6	5

[v-yulgu-msft · Accepted solution]

Hi @aar0n,

Please try these measures:

Average for actual =
CALCULATE (
    AVERAGE ( Actual[Actual Value] ),
    ALLEXCEPT ( Actual, Actual[Name] )
)
Average for predicted =
CALCULATE (
    AVERAGE ( Predicted[Predicted Value] ),
    ALLEXCEPT ( Predicted, Predicted[Prediction Grade] )
)

diff = ABS([Average for actual]-[Average for predicted])

Rank =
CALCULATE (
    RANKX ( ALL ( Predicted ), [diff],, ASC, DENSE ),
    ALLEXCEPT ( Predicted, Predicted[Prediction Grade] )
)

To create the line chart, you should add both [Month] field and [Prediction Grade] into X-Axis. And add [Rank] measure into visual level filter, set its value to 1.

I have uploaded the .pbix file for your reference.

Best regards,

Yuliana Gu

[aar0n]

It Seems to work great on the sample file, but i'm having issues in my file where as soon as i filter it to "1", the graph disappears. 

i will just have to play around in the next day or two and see what could be causing that.

thank you very much for the help!!!