DAX

[Kishorb]

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

[Greg_Deckler]

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

[Kishorb]

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

[v-yuta-msft · Accepted solution]

@Kishorb,

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.

[Kishorb]

Thanks it's working for me

[v-yuta-msft]

@Kishorb，

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