Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
spandy34
Responsive Resident
Responsive Resident

TOP 5 Values of a Text Field

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 

 

spandy34_0-1698164646778.png

 

4 ACCEPTED SOLUTIONS
EylesIT
Resolver II
Resolver II

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

 

EylesIT_0-1698168284276.png

 

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.

 

View solution in original post

spandy34
Responsive Resident
Responsive Resident

Hi thank you so much for your response

 

I tried the DAX and it is returning 

spandy34_1-1698170063386.png

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

 

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

 

spandy34_2-1698170130948.png

 

 

View solution in original post

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

 

 

View solution in original post

spandy34
Responsive Resident
Responsive Resident

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)

View solution in original post

6 REPLIES 6
EylesIT
Resolver II
Resolver II

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

 

EylesIT_0-1698168284276.png

 

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
Responsive Resident
Responsive Resident

Hi thank you so much for your response

 

I tried the DAX and it is returning 

spandy34_1-1698170063386.png

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

 

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

 

spandy34_2-1698170130948.png

 

 

@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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors