Getting Correct Formatting When Ranking Data Using TopN and Summarize

[savannahmac]

Hi all! 

I am working on writing Alt Text for the visualizations on my report. I have a table that looks similar to this on one page: 

Question	Percent Pos
Question 1 Text	99.80%
Question 2 Text	99.20%
Question 3 Text	98.40%

This table is already sorted (through the filters panel) to show the top 3 questions, ranked by the Percent Positive column (there are about 15 questions total). I've written this formula to get the questions ranked and it text format for the Alt Text: 

Questions for AT = CONCATENATEX(

    TOPN(

        3,

        SUMMARIZE(

            'Areas Final',

            'Areas Final'[Question],

            "Strengths", AVERAGE('Areas Final'[Percent Pos])

            ),

            [Strengths], DESC

            ), 'Areas Final'[Question], ", ", [Strengths], DESC)

This returns exactly what I need: "Question 1, Question 2, Question 3". 

However, I've written something similar to get the values: 

Values for AT = CONCATENATEX(

    TOPN(

        3,

        SUMMARIZE(

            'Areas Final',

            'Areas Final'[Percent Pos],

            "StrengthsPerc", CALCULATE((AVERAGE('Areas Final'[Percent Pos]))*100)

            ),

            [StrengthsPerc], DESC

            ), 'Areas Final'[Percent Pos], ", ", [StrengthsPerc], DESC)

and I get the correct values, in the correct order: "0.998, 0.992, 0.984".

However, I cannot seem to get them formatted into ##.##%. The original column being summarized ('Areas Final'[Percent Pos]) is formatted correctly and appears as a percentage. However, I can't figure out where/how to format this result to have the numbers appear correctly. Any help is greatly appreciated!!!

Thank you!

Savannah

[Chris_White · Accepted solution]

CONCATENATEX is converting your values to text.  Therefore, there are no options in the 'Measure Tools' tab of the ribbon which will help as, after the measure is calculated, Power BI is now just seeing the output as text, and not as a number.

What we need to do is use the FORMAT function inside your existing DAX to apply the right formatting.  This will be something like replacing:
'Areas Final'[Percent Pos], ", ", [StrengthsPerc]

with
FORMAT('Areas Final'[Percent Pos], "0.00%" ), ", ", [StrengthsPerc]

More details on format in case you need to tweak the formatting:
https://learn.microsoft.com/en-us/dax/format-function-dax

[savannahmac]

Thanks so much for your solution! This worked perfectly.