Forum Discussion
Helper IVFormat 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)
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!
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.
4 Replies
- amitchandak
Super User
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"})
)
akhilduvvuruamitchandak - 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"}) & ""
)
v-yadongf-msftCommunity Support
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.
