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 )
    )
)
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:

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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 )
    )
)