Display top 10 discrepancies in table

[brawndo]

I have a table that shows me estimates of a project and the actual cost. In some cases the estimate is below the actual cost and vise versa. I created a measure to show the variances between the actuals and the estimates.  How can I create a table that will show me the top 10 values regardless of them being negative or positive numbers.

[Ashish_Mathur · Accepted solution]

Hi,

Create a measure to calculate the absolute variance

Absolute variance = If([variance]<0,-[variance],[variance])

Now use the RANKX() function to rank the Absolute variance measure.  Then apply a filter on the RANKX measure to show only Top 10 values.

Hope this helps.

[V-lianl-msft · Accepted solution]

Hi @brawndo ,

As @Ashish_Mathur  Said before, For more details, you can refer to this pbix

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

[brawndo]

Thank you @Ashish_Mathur and @V-lianl-msft  so much! This has helped me tremendously. There is a separate issue I'm running into. I want to expand upon this. I have four different regions and I want to show the top 10 in each region. 

[brawndo]

@Ashish_Mathur  @V-lianl-msft . I just solved my previous question using the following.

Sandbox Top 10 = CALCULATE([Top 10 Values], 
FILTER(VALUES('Cost Report Plan vs Actual 1201'[WR Number]),
IF(RANKX(ALL('Cost Report Plan vs Actual 1201'[WR Number]), [Top 10 Values],,DESC) <= 'Ranking Selections'[Ranking Select],[Top 10 Values], BLANK() ) ) )

 With this slicer.

Ranking Select = IF(HASONEVALUE('Ranking Selections'[Ranking]), VALUES('Ranking Selections'[Rank Number]), 100000)

[Ashish_Mathur · Accepted solution]

Hi,

Create a measure to calculate the absolute variance

Absolute variance = If([variance]<0,-[variance],[variance])

Now use the RANKX() function to rank the Absolute variance measure.  Then apply a filter on the RANKX measure to show only Top 10 values.

Hope this helps.