Forum Discussion

akhilduvvuru's avatar
akhilduvvuru
Icon for Helper IV rankHelper IV
3 years ago
Solved

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

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) V...
Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

akhilduvvuru's avatar
akhilduvvuru
Icon for Helper IV rankHelper IV
3 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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"}) & ""
)