Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |