CODEWITHSUNDEEP
powerbidax
Keen9
Keen9

Reputation: 11

Dax / Power BI Issues with subtracting percentage values

I am trying to create a table where I show some KPI's compared with budget. My KPI's are fairly simple, like:

FTE = Calculate(SUM(Table1[FTE]), Table1[Type]="Actual")
FTE BU = Calculate(SUM(Table1[FTE]), Table1[Type]="Budget")
Profitability = DIVIDE([Result], [Revenues],0)
Profitability BU = DIVIDE([Result BU], [Revenues BU],0)

("Result" and "Revenus" are also simple measures with just a "calculate(sum...")

I try to visualize this in a table with 4 columns. The first column, named "Template[Name]" shows the KPI's Name, like "FTE" and "Profitability", the next column shows actual vaulus, the third budget values and the fourth the difference. I do this by creating measures containing all my KPI's, like:

KPI = switch(
SELECTEDVALUE(Template[Name]),
"FTE", [FTE],
"Profitability", [Profitability],
""
)

and

KPI BU = switch(
    SELECTEDVALUE(Template[Name]),
    "FTE", [FTE BU],
    "Profitability", [Profitability BU],
    ""
    )

The last column, the difference, is just:

KPI Dif = IFERROR([KPI] - [KPI BU],"Error")

This works very well. When I but all these four in a Power BI-table I get:

Name               KPI    KPI BU      KPI Dif
FTE                 50      52          -2
Profitability      0,04     0,13      -0,09

However, I want to show the profitability figures as percentage, but then I cant get the difference column to work. When I change "KPI" and "KPI BU" to

KPI = switch(
    SELECTEDVALUE(Template[Name]),
    "FTE", [FTE],
    "Profitability", Format([Profitability],"percent"),
    ""
    )

it gives me the profitability figures in percent but an error in the Dif-column:

Name               KPI    KPI BU      KPI Dif
FTE                 50      52          -2
Profitability      4%      13%         Error

I have also tried to change both my profitability measures to:

Profitability = Format(DIVIDE([Result], [Revenues],0),"percent")

but it gives me the same error.

What must I do to show some KPI's i number format and some KPI's in percentage format?

Upvotes: 0

Views: 562

Answers (1)

Keen9
Keen9

Reputation: 11

Funny how you can sit with a problem a whole day, and when you reach out for help, you figure it out 15 min later yourself...

I solved my problem by changing my measure "KPI Dif" to:

KPI Dif = IF(HASONEVALUE(Template[Name]),
SWITCH(VALUES(Template[Name]),
"FTE", [KPI] - [KPI BU],
"Profitability", FORMAT([KPI] - [KPI BU], "percent"),
""))

Upvotes: 0

Related Questions