TOP 5 Values of a Text Field

[spandy34]

Could someone please help me.  I have the table below and I would like a masure that returns the top 3 Industry Names (Column Industry Name New) based on the OBS_VALUE field 

The result based on the table below would be Manufacturing, Health, Professional Scientific & technical.

@tamerj1 @amitchandak 

[EylesIT · Accepted solution]

@spandy34, here is my suggested solution.

Create the measure below:

Top 3 Industries = 
    CONCATENATEX(
        TOPN(3,
            SUMMARIZE(
                YourTable,
                YourTable[Industry Name New],
                "OBS", SUM(YourTable[OBS_VALUE]),
                "MAX_RECORD_OFFSET", max(YourTable[RECORD_OFFSET])
            ),
            [OBS], DESC, [MAX_RECORD_OFFSET], DESC
        ),
        YourTable[Industry Name New], ", ",
        [OBS], DESC, [MAX_RECORD_OFFSET], DESC
    )

Add a Card visual to your report, and drag the newly created measure onto it. With the data you provided, this gives the output below:

Note that in your data you have a tie for 3rd place between "Professional, scientific & technical" and "Retail" (both have OBS_VALUE 8000). How should your report handle this situation? In the measure, I have assumed that the industry with the higher RECORD_OFFSET will "win" a tie-break.

Hope this helps.

[spandy34 · Accepted solution]

Hi thank you so much for your response

I tried the DAX and it is returning 

But I would be expecting Manufacturing, Health, Professional Scientfitc & technical.

Do you have any idea what i may have done wrong?

[EylesIT · Accepted solution]

@spandy34, your DAX code above is summing OBS_VALUE from a different table.

Try replacing

SUM('Business Size_Sector'[OBS_VALUE])

with

SUM('Employment Sector'[OBS_VALUE])

[EylesIT · Accepted solution]

@spandy34, here is my suggested solution.

Create the measure below:

Top 3 Industries = 
    CONCATENATEX(
        TOPN(3,
            SUMMARIZE(
                YourTable,
                YourTable[Industry Name New],
                "OBS", SUM(YourTable[OBS_VALUE]),
                "MAX_RECORD_OFFSET", max(YourTable[RECORD_OFFSET])
            ),
            [OBS], DESC, [MAX_RECORD_OFFSET], DESC
        ),
        YourTable[Industry Name New], ", ",
        [OBS], DESC, [MAX_RECORD_OFFSET], DESC
    )

Add a Card visual to your report, and drag the newly created measure onto it. With the data you provided, this gives the output below:

Note that in your data you have a tie for 3rd place between "Professional, scientific & technical" and "Retail" (both have OBS_VALUE 8000). How should your report handle this situation? In the measure, I have assumed that the industry with the higher RECORD_OFFSET will "win" a tie-break.

Hope this helps.

[spandy34 · Accepted solution]

Hi thank you so much for your response

I tried the DAX and it is returning 

But I would be expecting Manufacturing, Health, Professional Scientfitc & technical.

Do you have any idea what i may have done wrong?

[EylesIT · Accepted solution]

@spandy34, your DAX code above is summing OBS_VALUE from a different table.

Try replacing

SUM('Business Size_Sector'[OBS_VALUE])

with

SUM('Employment Sector'[OBS_VALUE])

[spandy34]

Hi @EylesIT 

The DAX is working great but I just wondered if we could amend the DAX so instead of there being commas after every record, could there be a hard return so there is a seperate item on each line?

[spandy34 · Accepted solution]

Hi @EylesIT 

I have carried out some research and found putting UNICHAR10 in the DAX works below:-77

z4_Top 3 and Others =

    CONCATENATEX(

        topn(3,

        SUMMARIZE(

            'Employment Sector',

            'Employment Sector'[Industry Name New],

            "OBS",SUM('Employment Sector'[OBS_VALUE]),

            "MAX_RECORD_OFFSET", MAX('Employment Sector'[RECORD_OFFSET])

        ),

        [OBS],DESC,[MAX_RECORD_OFFSET], DESC

        ),

        'Employment Sector'[Industry Name New],"," & UNICHAR(10),

        [OBS],DESC,[MAX_RECORD_OFFSET],DESC)

[spandy34]

Thank you that is brilliant - its worked:) Thanks so much for your patience and help