Forum Discussion

M2M's avatar
M2M
Frequent Visitor
8 years ago
Solved

Top 1 Of Max

Hello,   I am a newbie to PowerBI trying to grasp some fundamental concepts.I have a sample dataset(screenshot and file attached) with the post.   What I am trying to do here is to get the Top1 o...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    M2M

     

    If you have many branches..then may be you can use this

     

    New Table =
ADDCOLUMNS (
    SUMMARIZE ( Sample1, Sample1[Branches], "Max Total", MAX ( Sample1[Total] ) ),
    "Max Book Resale", CALCULATE (
        MAX ( Sample1[Book Resale - SALE] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    ),
    "Inside Sales", CALCULATE (
        MAX ( Sample1[Inside Sales.Inside Sales Name] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    ),
    "Manufacturer", CALCULATE (
        MAX ( Sample1[Manufacturer Inside Sales Person] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi M2M

     

    This is an even better way of achieving the results

     

    Table =
GENERATE (
    SELECTCOLUMNS (
        SUMMARIZE ( Sample1, Sample1[Branches] ),
        "MyBranches", [Branches]
    ),
    CALCULATETABLE (
        TOPN ( 1, Sample1, Sample1[Total], DESC, Sample1[Book Resale - SALE], DESC )
    )
)
M2M's avatar
M2M
Frequent Visitor
8 years ago

Please any help on this ?

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi M2M,

     

    Did you mean this?

     

    Create a new calculated table with DAX TOPN() function.

     

    Top 1 =
TOPN (
    1,
    'sample',
    'sample'[Total], DESC,
    'sample'[Book Resale - SALE], DESC,
    'sample'[Booked Manufacturer Inside Sales Person], DESC
)

     

    Thanks,
    Xi Jin.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      M2M

       

      If you have many branches..then may be you can use this

       

      New Table =
ADDCOLUMNS (
    SUMMARIZE ( Sample1, Sample1[Branches], "Max Total", MAX ( Sample1[Total] ) ),
    "Max Book Resale", CALCULATE (
        MAX ( Sample1[Book Resale - SALE] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    ),
    "Inside Sales", CALCULATE (
        MAX ( Sample1[Inside Sales.Inside Sales Name] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    ),
    "Manufacturer", CALCULATE (
        MAX ( Sample1[Manufacturer Inside Sales Person] ),
        Sample1[Total] = EARLIER ( [Max Total] )
    )
)
      • M2M's avatar
        M2M
        Frequent Visitor
        8 years ago

        Thanks Zubair.I had to tweak your solution a little bit to get it working, :smileyhappy: