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.
HI , I need a help in correcting measure defination which was display 'second lowest value' into a table but when i used this measure into Card visual then blank() value displayed.
I have created below measure which is calculated Deptname and first lowest and second lowest values into table visuals based on SalesRep Slicer values selection
1. SalesDeptName = if(isfiltered('Sales Data'[SalesRep]),selectedvalue('Sales Data'[Dep]))
2. First Lowest Target = Calculate(Calculate(Min('Sales Data'[Target]),AllExcept('Sales Data','Sales Data'[Dep])),filter('Sales Data','Sales Data'[Dep] =[SalesDeptName]))
3. MRank2 = RANKX(filter(all('Sales Data'),'Sales Data'[Dep] = selectedvalue('Sales Data'[Dep])),Calculate(Sum('Sales Data'[Target])),,ASC,dense) = 2
4.Second lowest Quota = if( Calc[MRank2] = true(),Calculate(Sum('Sales Data'[Target])))
Please advice me on this
Thanks
Can you pls advice me on this
Solved! Go to Solution.
Use calculate column instead of measure to achieve the rank:
MRank2 = RANKX ( FILTER ( 'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep]) ), RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense) )
Then modify the measure like below:
Second lowest Quota = VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1)) RETURN CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you share example data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
I have shared below sample data table for reference
SalesManager | SalesRep | Dep | Target |
Peter | Andrew | Prop1 | 210000 |
Peter | Macman | Prop1 | 280000 |
Peter | Alex | Prop1 | 210000 |
Peter | Jack | Prop1 | 78000 |
Peter | Joe | Prop1 | 120000 |
Peter | peter | Prop1 | 120000 |
Peter | Adam | Prop1 | 120000 |
Peter | Stephanie | Prop1 | 210000 |
Peter | Kirsten | Prop1 | 350000 |
Lenda | Ajay | Pro2 | 5000000 |
Lenda | Rocky | Pro2 | 3000000 |
Lenda | Pratik | Pro2 | 3500000 |
Lenda | Harshal | Pro2 | 1000000 |
Lenda | Das | Pro2 | 2500000 |
Lenda | Vivek | Pro2 | 1700000 |
Lenda | Lina | Pro2 | 1500000 |
Lenda | Dhoni | Pro2 | 1000000 |
Use calculate column instead of measure to achieve the rank:
MRank2 = RANKX ( FILTER ( 'Sales Data', 'Sales Data'[Dep] = EARLIER('Sales Data'[Dep]) ), RANKX ( ALL ( 'Sales Data' ), 'Sales Data'[Target],, DESC, Dense) )
Then modify the measure like below:
Second lowest Quota = VAR lowest_count = COUNTROWS(FILTER('Sales Data', 'Sales Data'[MRank2] = 1)) RETURN CALCULATE(MAX('Sales Data'[Target]), FILTER(ALL('Sales Data'), 'Sales Data'[MRank2] = lowest_count + 1))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks it's working for me
Great to hear that. Could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.
Regards,
Jimmy Tao
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |