Format Error while displaying numbers and percentages at same time in a table

[akhilduvvuru]

Hi Team, I have a reqirement to show both numbers and percentages in same column using a measure as below:
I wrote a switch statement in measure

Measure = 
VAR _Cat = SELEECTEDVALUE(CATEGORY)
VAR _Apple = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
VAR _Orange = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
RETURN
SWITCH (
TRUE (),
_Cat = "Fruits Total", CALCULATE(SUM(FACT_TABLE[SALES]), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"}),
_Cat = "Total %", CALCULATE( FORMAT((_Apple-_Orange)/_Apple,"0.0%"), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"})
)

Expected Output:

Category	Sales
Apple	22
Orange	10
Fruits total	33 (Add Apple and Orange)
Total %	54.5% (Apple-Orange)/Apple

However, my above measure is giving the right result as expected but getting the following conversion error.

MdxScript(Model (113, 17) Calculation error in measure 'Measure': Cannot convert value '54.5%' of  type Text to type Numeric/Date.

Not sure on how to handle this. Please help me resolving the same. Thanks!

[v-yadongf-msft · Accepted solution]

Hi @akhilduvvuru ,

As a workaround, please try following steps.

This is my test table:

Create a new column:

Column =
VAR _a = 'Table'[SALES] * 100
VAR _b =
    IF ( 'Table'[CATEGORY] = "Total %", _a, 'Table'[SALES] )
VAR _c =
    CONVERT ( _b, STRING )
RETURN
    IF ( 'Table'[CATEGORY] = "Total %", _c & "%", _c )

You will get a column like this:

I think this is the result you want:

Please refer the attached pbix file.

Best regards,

Yadong Fang

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

[amitchandak]

@akhilduvvuru , you can not have two data types in one column. So other one need to a text too, you use format in true result also

Measure =
VAR _Cat = SELEECTEDVALUE(CATEGORY)
VAR _Apple = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
VAR _Orange = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
RETURN
SWITCH (
TRUE (),
_Cat = "Fruits Total", CALCULATE(SUM(FACT_TABLE[SALES]), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"}) & "",
_Cat = "Total %", CALCULATE( FORMAT((_Apple-_Orange)/_Apple,"0.0%"), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"})
)

[akhilduvvuru]

@amitchandak - Thanks for your quick response. I updated my measure with &"". But not luck it is still showing the same error

MdxScript(Model (113, 25) Calculation error in measure 'Measure': Cannot convert value " of  type Text to type Number.

I have also added the same thing to else part (populating numbers only) as well. I think this time else part is throwing this above error.

[amitchandak]

@akhilduvvuru , That is strange

tr y like

Measure =
VAR _Cat = SELEECTEDVALUE(CATEGORY)
VAR _Apple = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
VAR _Orange = CALCULATE(SUM(SALES),REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple"})
RETURN
SWITCH (
TRUE (),
_Cat = "Fruits Total", CALCULATE(SUM(FACT_TABLE[SALES]), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"}) & "",
_Cat = "Total %", CALCULATE( FORMAT((_Apple-_Orange)/_Apple,"0.0%"), REMOVEFILTERS(DIM_TABLE), DIM_TABLE[CATEGORY] IN {"Apple","Orange"}) & ""
)