Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Obtaining Top 3 by Amounts

Hi, using the table below, I need to obtain the top 3 categories by Amount grouped by Location please. How can this be done?

 

Table1

LocaitonCategoryAmount
BirminghamBike£1,400
Birminghame-Bike£2,300
BirminghamScooter£700
BirminghamKayak£1,900
BirminghamCanoe£800
LiverpoolBike£3,000
Liverpoole-Bike£1,900
LiverpoolScooter£800
LiverpoolKayak£6,000
LiverpoolCanoe£4,000
GlasgowBike£700
Glasgowe-Bike£800
GlasgowScooter£1,900
GlasgowKayak£2,300
GlasgowCanoe£1,200


Top3 Categories by Amount, grouped by countries:

LocaitonTop_3_CategoryAmount
Birminghame-Bike£2,300
BirminghamKayak£1,900
BirminghamBike£1,900
LiverpoolKayak£6,000
LiverpoolCanoe£4,000
LiverpoolBike£3,000
GlasgowKayak£2,300
GlasgowScooter£1,900
GlasgowCanoe£1,200


Thanks

Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi RichOB,
    Please use the DAX logic to get Top3 Categories by Location by Amount.

    Top3_Amount = 
VAR CurrentCategory = SELECTEDVALUE('Table'[Category])
VAR CurrentLocation = SELECTEDVALUE('Table'[Locaiton])
VAR RankCategory =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Locaiton] = CurrentLocation
        ),
        CALCULATE ( SUM ( 'Table'[Amount] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF (
        RankCategory <= 3,
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

5 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Icon for Super User rankSuper User
    1 year ago

    Hi RichOB,
    Please use the DAX logic to get Top3 Categories by Location by Amount.

    Top3_Amount = 
VAR CurrentCategory = SELECTEDVALUE('Table'[Category])
VAR CurrentLocation = SELECTEDVALUE('Table'[Locaiton])
VAR RankCategory =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Locaiton] = CurrentLocation
        ),
        CALCULATE ( SUM ( 'Table'[Amount] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    IF (
        RankCategory <= 3,
        CALCULATE ( SUM ( 'Table'[Amount] ) )
    )

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

     

  • v-menakakota's avatar
    v-menakakota
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi RichOB  , 

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    I would also take a moment to thank ajaybabuinturi , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

     

    Best Regards, 
    Menaka Kota 
    Community Support Team

     

    • v-menakakota's avatar
      v-menakakota
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi  RichOB ,

      May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

       

      Thank you. 

      • v-menakakota's avatar
        v-menakakota
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi  RichOB ,

        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution so that other community members can find it easily. 

         
        Thank you. 

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    Hello RichOB 

    Create a Rank Measure

    Category Rank by Location =

    VAR CurrentCategory = SELECTEDVALUE('Table'[Category])

    VAR CurrentLocation = SELECTEDVALUE('Table'[Locaiton])

    RETURN

        RANKX(

            FILTER(

                ALL('Table'),

                'Table'[Locaiton] = CurrentLocation

            ),

            CALCULATE(SUM('Table'[Amount])),

            ,

            DESC,

            DENSE

        )

     

    Create the Final Measure for Top 3 Amounts

    Top 3 Amounts by Location =

    VAR RankValue = [Category Rank by Location]

    RETURN

        IF(RankValue <= 3, CALCULATE(SUM('Table'[Amount])))

     

    Thanks

     Pankaj Namekar | LinkedIn

    If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.