Rank Column then Calculate Running Percentage and Text

[NiugeS]

Hi all,

I'm still working my way through PowerBI and i'm stumped.  I've created the below, which to my amazement, works.  It's a list of teams and the total they have paid.  I have then created two additional columns % of Total Sales and % of Location Sales.

I am now trying to find a way to rank the % of Location Sales Amount Largest to Smallest and calculate a running total.  Once I have the running total, I want to add a column that specifies what Tier the team is in.

Tier Info:

Tier 1 <= 25%
Tier 2 >25% <= 50%
Tier 3 > 50% <=75%
Tier 4 > 75%

Example below (filtered to Canada), the teams are put in order from highest to lowest spend based on % of location Sales Amount, a running total of location % is calculated and based on this figure, the Tier information is provided.

I have attached my test pbix file for reference. Test Pbix 

Any guidance or suggestions would be greatly appreciated.

Many thanks...

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[NiugeS]

@Ashish_Mathur Thank you for the help.  I'll try and have a look later this evening.

[mahoneypat]

Here is a measure expression that gets your desired result.

Tier =
VAR vThisTeam =
    MIN ( 'Table'[Team] )
VAR vSummary =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table'[Team] ),
        "cTotal", [% of Location Sales Amount]
    )
VAR vThisTotal = [% of Location Sales Amount]
VAR vRT =
    SUMX (
        FILTER (
            vSummary,
            [cTotal] >= vThisTotal
        ),
        [cTotal]
    )
RETURN
    SWITCH (
        TRUE (),
        vRT <= 0.25, "Tier 1",
        vRT <= 0.50, "Tier 2",
        vRT <= 0.75, "Tier 3",
        "Tier 4"
    )

Pat

[NiugeS]

Thank you @mahoneypat - will be reviewing tonight.  Greatly appreciate the help.

[NiugeS]

@mahoneypat  Hi - i tried to edit it before anyone viewed but you were too quick!  I've uploaded the correct one.  Thank you.

[mahoneypat]

I think you attached a different pbix file.

Pat