Top 1 Of Max

[M2M]

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 of the Max Total for the branches.The sample dataset has only one branch there can be n number of such branches.The Dax query should return the row that I have highlighted in the screesnshot.It is similar to using Row Number parition in SQL

Data : Sample Data

[Zubair_Muhammad · Accepted solution]

@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]

Please any help on this ?

[v-xjiin-msft]

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 · Accepted solution]

@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]

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

[Zubair_Muhammad · Accepted solution]

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