Forum Discussion

insandur's avatar
insandur
Icon for Helper II rankHelper II
4 years ago
Solved

Top N Power bi

I am Trying to get the "Code" which has maximum quntity or most Stock Qty in the table and should get filtered based Entity.

 

Table name : 'This month''s Stock Out'

Stock QtyMonthCodeSupplierEntity
210BLRABG2
210APAGBCG1
210PIO67BCG1
010  G2
110TR28GCZG1

 

i tried below code but dont get filtered on the Entity.

Stock Qty Code = TOPN(1,
CALCULATETABLE('This month''s Stock Out',
'This month''s Stock Out'[Month]=MONTH(TODAY()-1)),
'This month''s Stock Out'[Stock Qty])
  • amitchandak's avatar
    amitchandak
    4 years ago

    insandur , First Create a measure with help from a separate month table joined with month of your table

     

    this month = calculate(Sum(Table[Stock Qty]), filter(all(Month), Month[Month] = max(Month[Month] )) )

     

    TOP1 Name= CALCULATE([this month], TOPN(2,ALLSELECTED('Table'[Code]), [this month ] ,DESC), values('Table'[Code]))

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    insandur , First Create a measure with help from a separate month table joined with month of your table

     

    this month = calculate(Sum(Table[Stock Qty]), filter(all(Month), Month[Month] = max(Month[Month] )) )

     

    TOP1 Name= CALCULATE([this month], TOPN(2,ALLSELECTED('Table'[Code]), [this month ] ,DESC), values('Table'[Code]))