Dynamically changing the number of decimal points and applying conditional prefix/suffix

[RichardJ]

Hi,

In the linked PBIX file I have a table with metrics, target, actual and delta values.

I'm wondering if there is a way of dynamically applying a format using a measure where

1) the 'Percentage' would appear with the suffix of the percentage symbol (e.g. 22.00 % - two decimal places)

2) the 'Cost' would appear with a prefix of the currency symbol (e.g. $ 71472- whole number, no decimal places)

I have a measure for the 'delta' but am stuggling to understand if it's possible to apply conditions to it to enable conditions 1 and 2 above.

Thanks for any advice,

PBIX full link : https://www.dropbox.com/s/b907wyhzrflugkp/Power_BI_Dynamic_Formatting_Question.pbix?dl=0

Richard

[mahoneypat · Accepted solution]

You could create variables that have a FORMAT or CONVERT function to conditional return those, but the better way is to use a Calculation Group with dynamic format expressions.

(6) Are you still not using Calculation Groups?! - YouTube

Pat

[VahidDM · Accepted solution]

Hi @RichardJ 

Try this measure with Format dax code:

Delta = 
VAR _Delta =
    MAX ( 'Table'[Target] ) - MAX ( 'Table'[Actual] )
RETURN
    IF (
        MAX ( 'Table'[Metric] ) = "Cost",
        FORMAT ( _Delta, "$#,##0" ),
        IF (
            MAX ( 'Table'[Metric] ) = "Percentage",
            FORMAT ( _Delta, "%##.00" ),
            _Delta
        )
    )

Output will be as below:

If you want to add specific condition for any metrics, try to use SWITCH rather that if in the mesure.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos  !!

[RichardJ · Accepted solution]

@Vahid Thank you very much for this solution.
It was really helpful.
Ended up using this

Delta = 
VAR _Delta =
    SUM ( 'Table'[Target] ) - SUM ( 'Table'[Actual] )
RETURN
    IF (
        MAX ( 'Table'[Metric] ) = "Cost",
        FORMAT ( _Delta, "$ #,##0" ),
        IF (
            MAX ( 'Table'[Metric] ) = "Percentage",
            FORMAT ( _Delta/100, "0%" ),
            _Delta
        )
    )

and got the results I was looking for

.

Appreciate you making the effort to respond.

Cheers,

Richard

[VahidDM · Accepted solution]

Hi @RichardJ 

Try this measure with Format dax code:

Delta = 
VAR _Delta =
    MAX ( 'Table'[Target] ) - MAX ( 'Table'[Actual] )
RETURN
    IF (
        MAX ( 'Table'[Metric] ) = "Cost",
        FORMAT ( _Delta, "$#,##0" ),
        IF (
            MAX ( 'Table'[Metric] ) = "Percentage",
            FORMAT ( _Delta, "%##.00" ),
            _Delta
        )
    )

Output will be as below:

If you want to add specific condition for any metrics, try to use SWITCH rather that if in the mesure.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your Kudos  !!

[RichardJ · Accepted solution]

@Vahid Thank you very much for this solution.
It was really helpful.
Ended up using this

Delta = 
VAR _Delta =
    SUM ( 'Table'[Target] ) - SUM ( 'Table'[Actual] )
RETURN
    IF (
        MAX ( 'Table'[Metric] ) = "Cost",
        FORMAT ( _Delta, "$ #,##0" ),
        IF (
            MAX ( 'Table'[Metric] ) = "Percentage",
            FORMAT ( _Delta/100, "0%" ),
            _Delta
        )
    )

and got the results I was looking for

.

Appreciate you making the effort to respond.

Cheers,

Richard

[mahoneypat · Accepted solution]

You could create variables that have a FORMAT or CONVERT function to conditional return those, but the better way is to use a Calculation Group with dynamic format expressions.

(6) Are you still not using Calculation Groups?! - YouTube

Pat

[RichardJ · Accepted solution]

@mahoneypat The video was awesome. Another great solution and not one I would have considered.

Never looked at calculation groups in detail before.

Thanks for taking the time to reply and for making the video.

I've subscribed to your channel.

Cheers,

Richard