DAX code to change number format based on % or No. adds two lines to table

[MCosenza]

Hi Everyone, I'm using the below SWITCH code to format a value depending whether it's a percentage or a whole number. Basically I want to show values with a % with a decimal and whole numbers as whole numbers. For some reason though when I include this in the table it generates two lines for each item in the list where it should be showing just one. This then impacts the charts linked to the table.

Any thoughts on what might be the cause of this as I am out of ideas. Thanks.

Formatted Actual = SWITCH (

      TRUE(),

      'KPI Actuals'[Measure Type] = "%", FORMAT('KPI Actuals'[value], "0.0") & "%",

      'KPI Actuals'[Measure Type] = "No", FORMAT('KPI Actuals'[value], "0"),

      BLANK()

)

[amitchandak · Accepted solution]

@MCosenza , is this measure or column. In case it is measure, I think it should only show one row

If these are measures value and measure type, a measure can be

Formatted Actual = SWITCH (
TRUE(),
max('KPI Actuals'[Measure Type]) = "%", FORMAT(Sum('KPI Actuals'[value]), "0.0") & "%",
Max('KPI Actuals'[Measure Type]) = "No", FORMAT(Sum('KPI Actuals'[value]), "0"),
BLANK()
)