Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to 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.
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?
@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])
Hi @EylesIT
I have carried out some research and found putting UNICHAR10 in the DAX works below:-77
@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.
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?
@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])
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?
Hi @EylesIT
I have carried out some research and found putting UNICHAR10 in the DAX works below:-77
Thank you that is brilliant - its worked:) Thanks so much for your patience and help
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |