Forum Discussion

aar0n's avatar
aar0n
Icon for Advocate II rankAdvocate II
8 years ago
Solved

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.   Basically, what...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
aar0n
Icon for Advocate II rankAdvocate II
8 years ago

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