Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

top 3 record in table

refer below table

SEGMENT CATEGORY SUB_CATEGORY VALUE 
EAST A CH 10 
EAST B AB 20 
EAST C  LO 30 
EAST D MN 10 
EAST E PQ 40 
EAST F GH 50 
EAST G FL 100 
EAST H KP 50 
EAST I IO 80 
EAST J MN 60 
EAST K ZX 70 
EAST L HM 90 
EAST M RO 45 
WEST N PE 62 
WEST O SC 34 
WEST P EM 20 
WEST Q CH 40 
WEST R UV 67 
WEST S IR 73 
WEST TU RE 50 
WEST X DE 74 
CENTRAL W BC 88 
CENTRAL V HG 92 
CENTRAL Y IQ 106 
CENTRAL Z AB 110 
CENTRAL K PK 23 
CENTRAL L ST 54 
SOUTH M YS 18 
SOUTH O JS 60 
SOUTH Q SE 30 
SOUTH E PO 40 
SOUTH A CE 20 
SOUTH B HI 10 
SOUTH F LI 200 
SOUTH G BM 50 
SOUTH H GM 150 
SOUTH R HM 20 
SOUTH S TY 30 
SOUTH V UT 50 

Need below output,Top 3 category based on value in each segment as follows,

 

 

Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below measure and the attached pbix file.

    It is for creating a new measure.

     

    Value top three: = 
VAR currentsegment =
    MAX ( Segment[SEGMENT] )
VAR newtable =
    FILTER ( ALL ( Data ), Data[SEGMENT] = currentsegment )
RETURN
    CALCULATE (
        SUM ( Data[VALUE] ),
        KEEPFILTERS ( TOPN ( 3, newtable, CALCULATE ( SUM ( Data[VALUE] ) ), DESC ) )
    )

2 Replies

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

    Hi,

    Please check the below measure and the attached pbix file.

    It is for creating a new measure.

     

    Value top three: = 
VAR currentsegment =
    MAX ( Segment[SEGMENT] )
VAR newtable =
    FILTER ( ALL ( Data ), Data[SEGMENT] = currentsegment )
RETURN
    CALCULATE (
        SUM ( Data[VALUE] ),
        KEEPFILTERS ( TOPN ( 3, newtable, CALCULATE ( SUM ( Data[VALUE] ) ), DESC ) )
    )