topic Re: TOP 5 Values of a Text Field in DAX Commands and Tips

TOP 5 Values of a Text Field

spandy34 — Tue, 24 Oct 2023 17:10:26 GMT

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

Re: TOP 5 Values of a Text Field

EylesIT — Tue, 24 Oct 2023 17:27:27 GMT

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.

Re: TOP 5 Values of a Text Field

spandy34 — Tue, 24 Oct 2023 17:56:42 GMT

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?

Re: TOP 5 Values of a Text Field

EylesIT — Tue, 24 Oct 2023 18:19:27 GMT

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

Re: TOP 5 Values of a Text Field

spandy34 — Tue, 24 Oct 2023 19:43:51 GMT

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

Re: TOP 5 Values of a Text Field

spandy34 — Wed, 25 Oct 2023 10:48:47 GMT

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?

Re: TOP 5 Values of a Text Field

spandy34 — Wed, 25 Oct 2023 17:08:56 GMT

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)