Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aar0n
Advocate II
Advocate II

Filter that will Select the Closest Related Predicted Value

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.

1.PNG

 

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 GradeMonth Predicted Value
A18
A29
A315
A413
A59
A616
A713
A88
A914
A1012
A118
A1215
B121
B228
B313
B410
B520
B630
B728
B824
B920
B1013
B1116
B125

 

 

 

Actual Table

NameMonthActual Value
111122
111229
111315
11145
111520
111634
222110
22229
222311
222412
222515
22265

 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.