Skip to main content
cancel
Showing results for 
Search instead 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

Reply
planbitionbi
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):
dax.png
 
1 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]))

View solution in original post

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hope this helps:

 

dax2.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors