Formatting result of DIVIDE function as percentage

Question

I have a table report in Power BI where I would like to present 'Percentage of Estimate Used.' The report shows multiple drill-down levels, so I attempted to use the DIVIDE function in from DAX to manage scope issues and also provide some helpful text when estimates might be missing from the data set.

Here is the DAX:

Percent Used = 
DIVIDE(
    SUM('Actuals by Project'[Used Amount])
    , SUM('Estimate Data'[Estimated_Amount])
    , "(no estimate)"
)

The math works fine, and the alternate text appears where it should. But I'd like to format the result as a percentage. The model regards the output of the DIVIDE function as text, because of the alternate text "(no estimate)" I assume. Is there a way to format the result as a percentage?

I'd like 0.1234... to appear as 12.34%.

Thanks!

Solution

I believe you are correct. A measure can't return multiple data types, so it has to return the percentage as text.

Luckily, you can use the FORMAT function like so:

Percent Used = 
FORMAT(
    DIVIDE(
        SUM('Actuals by Project'[Used Amount])
        , SUM('Estimate Data'[Estimated_Amount])
        , "(no estimate)"
    ),
"Percent")

If you want a different number of decimal places, then you can manually specify the format style like this FORMAT([...], "0.0%") or this FORMAT([...], "0.000%") etc.

Comments

[Ryan B.]

Something in the syntax isn't quite right, but I'll keep working with this. Thanks Alexis!

[Alexis Olson]

@Greenspark Oh, I forgot it was inside of a DIVIDE. It needs to go on the outside. See my edit.

[Ryan B.]

Heh, yeah, that busts out the aggregation into constituent rows ( crashing my computer along the way :P ) Such an annoying little problem.

[Ryan B.]

I changed the alternate value from "(no estimate)" to just the number 0. The measure now registers as a number and I'm able to apply the percent format. It would seem, for the moment, that using text in the alternate value and hoping to apply a number format to just the numeric result is asking too much.