Substract measure from other row in table

[planbitionbi]

Hi,

I have a simple Kpi Table with a dimension "KpiType". I want to show the user the KpiName and the value

Some kpi's are just a sum of the value, others need to be substracted. I created a measure for each kpi, like this one for example:

Netto beschikbare pool = CALCULATE(SUM('KPI'[KpiValue]), KpiType[KpiTypeName]= "Netto beschikbare pool")

Now I create the final "Kpi Measure" to put in my kpi table and re-use all measures I created for every single KPI. I want to use switch. This works greate for regular measure like above. But this doesn't work with measures with a formule like a distract or substract.

For example, I have these two formula which are re-using other kpi's, these are not working!

Nog vrij te geven = [Netto beschikbare pool]-[Maximale Afwezigheid tov bruto pool]

Tekort mensen tov aanvragen = ([Bezettingsaanvragen]-[Netto beschikbare pool])

The Kpi Formula look like this:

Kpi Waarde =

VAR CurrentItem = SELECTEDVALUE(KpiType[KpiTypeName])

RETURN

SWITCH( TRUE(),

CurrentItem = "Actieve Pool", [Actieve Pool],

CurrentItem = "Bruto Beschikbare Pool", [Actieve Pool],

CurrentItem = "Gepland Afwezig", [Gepland Afwezig],

CurrentItem = "Reeds gepland op dienst", [Reeds gepland op dienst],

CurrentItem = "Netto beschikbare pool", [Netto beschikbare pool],

CurrentItem = "Maximale afwezigheid tov bruto pool", [Maximale afwezigheid tov bruto pool],

CurrentItem = "Nog vrij te geven", [Nog vrij te geven],

CurrentItem = "Bezettingsaanvragen", [Bezettingsaanvragen],

CurrentItem = "Tekort mensen tov aanvragen", [Tekort mensen tov aanvragen],

SUM('KPI'[KpiValue]))

All kpi's that have a formula aren't showed in my table.

Screenshot(s):

[Greg_Deckler]

It's very hard to tell what is going on without some sample data and information to recreate. Perhaps try switching items around in your SWITCH statement? I don't understand "showed under the ..." Is there a screen shot of that?

[planbitionbi]

Hope this helps:

[planbitionbi · Accepted solution]

Got the Answer 🙂

The trick was to ignore the row context!

Example:

Nog vrij te geven = CALCULATE([Netto beschikbare pool]-[Maximale Afwezigheid tov bruto pool], ALLEXCEPT(KpiType, KpiType[KpiTypeName]))