cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Substract measure from other row in table

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):

1 ACCEPTED SOLUTION
Frequent Visitor

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]))
3 REPLIES 3
Super User
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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hope this helps:

Frequent Visitor

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]))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.