Convert number to currency or percentage inside SUMX

[Anonymous]

Hi,

I want to convert a variable to currency or percentage inside SUMX and SWITCH, but it does not seems to work.

I need to convert TotalLabourRev to currency and LabourSalesPercen to percentage in below code:

Testing =
VAR TotalLabourRev = CALCULATE([Amount], FILTER( 'GL Account', 'GL Account'[GL Account Level2] ="Labour Revenue"))

VAR LabourSalesPercen = DIVIDE(TotalLabourRev, [Total Revenue], BLANK())
return
SUMX('ProfitLoss Measure Groups2',
SWITCH([ProfitLoss Group Rows Measure2],
6, TotalLabourRev,
7, LabourSalesPercen))

I should mension that the data source is from a tabular model. The measure [Amount] and  [Total Revenue] are form GL table. the table ProfitLoss Measure Groups2 has the list of the measures to have measures in the rows and column

I ve tried:

Format(TotalLabourRev, "Currency")

or 

Format(LabourSalesPercen "0.0")

But it gives error complaining about cann't have string inside SUMX function.

I need the result to be like below:

Measure                           Testing

----------------------------------------
Labour Revenue              $45,236
Labour Revenue %           32.50%

Can someone help please.

Can someone help please.

[Icey]

Hi @Anonymous ,

How about this?

Testing =
VAR TotalLabourRev =
    CALCULATE (
        [Amount],
        FILTER ( 'GL Account', 'GL Account'[GL Account Level2] = "Labour Revenue" )
    )
VAR LabourSalesPercen =
    DIVIDE ( TotalLabourRev, [Total Revenue], BLANK () )
VAR Result =
    SUMX (
        'ProfitLoss Measure Groups2',
        SWITCH (
            [ProfitLoss Group Rows Measure2],
            6, TotalLabourRev,
            7, LabourSalesPercen
        )
    )
RETURN
    SWITCH (
        [ProfitLoss Group Rows Measure2],
        6, "$" & Result,
        7, Result * 100 & "%"
    )

Best Regards,

Icey

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

[Anonymous]

That is perfect. Thank you so much.

I just needed to add some foramtting like below:

SWITCH([ProfitLoss Group Rows Measure2],

6, "$" & FORMAT(result,"#,0; (#,0)"),

7, format(result *100 , "0.0")&" %"

)

[Icey · Accepted solution]

Hi @Anonymous ,

Glad to hear that. You may help accept the replies making sense as solution above. Your contribution is highly appreciated.

Best Regards,

Icey

[Anonymous]

@Icey  I will definitely accept the the solution, but befor that I have got one more question related to the result.

I will be using the result for further calculations, but we have converted them to text, how can I convert them back to numeric?

I can create a new measure without the conversion ( as it was orginally), but is there another cleaner approach?

[Icey]

Hi @Anonymous ,

Try to create a measure like so:

Measure =
SWITCH (
    [ProfitLoss Group Rows Measure2],
    6, VALUE ( [Testing] ),
    7, VALUE ( SUBSTITUTE ( [Testing], "%", "" ) )
)

Best Regards,

Icey

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

[Anonymous]

Thanks @Icey .

I really appreciate your help.

[mahoneypat]

Please see these links about dynamically changing format in DAX expressions:

https://www.kasperonbi.com/dynamic-format-using-dax/

https://docs.microsoft.com/en-us/dax/pre-defined-numeric-formats-for-the-format-function

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat 

That does not work, the measure is inside SUMX function, so it needs to be converted to value first, even then the conversion is not working, I have tried this:

value(FORMAT(TotalLabourRev, "$0")

it just return the figure with out adding the dollar sign.

[mahoneypat]

I don't usually see SWITCH inside a SUMX.  Can you say more about the table you are doing SUMX over and what makes that measure return 6 or 7?  Perhaps there is a different way to solve this.  Example data would be helpful too.

Regards,

Pat

[Anonymous]

@mahoneypat 

I am doing Finance's P&L, I need Actuall Amount, Budget Amount, LY Actuall Amount and so on.

These Amounts are break down by GL Accounts, until this point there are not any problem and it is ealy done.

When you need a percentage of a GL Accounts of the total sales in a row, that is where the difficulties starts

Account                 Actual Amount                Budget Amount
Labour                    $1,250                             $1,100
Parts                       $2,500                             $2,300
Total Sales               $3,750                            $3,400
Labour % of Sales    33.30%
Part % of Sales        66.67%

to have those percentages in rows, I had to create a new calculated table in the tabular model ( as in below), and that is where the

6 and 7 came from and it is used to in DAX I have sent.

As I said the source data is in the tabular model and it is not easy to recreat that.

Not sure if there is another way to do this report.