DAX-formula for calculating filtered field

[RemiAnthonise]

Hi guys,

I recently registered but I've been following this community for quite a long time. I've learned a lot of you guys, thank you for that.

Since I couldn't find the answer to my question I've decided to open this topic.

What I want to do:

- I have a field called Leavehours. In my example, this is 100. Let's say: an employee has 100 hours for 'leave'

- I have a field called Hours. The employee worked, for example, 8 hours

- I have a field called Category. The hours the employee worked can be classified in different categories. 

I'm creating a column called 'Leave'. I want to calculate the leavehours the employee had: column: Leave = Leavehours - Hours where Category = A-Verlof.

I've added a couple of images to give you guys an idea. DAX isn't my strong suit so if anybody can help, please!

edit: another issue under the images.

 

EDIT:

If you guys can help me and my problem is solved I think I'll have a new issue. As I mentioned, I've created a column called LeaveHours, which has as value 100. This is the 'start value' for an employee's leavehours. My next thing is:

I want to use the value '100' as starting value. I want to subtract the filtered hours with the category A-verlof from this value 100. Let's say an employee took 3 days off ( 8 hours a day, 3 * 8 = 24 ) so I want to see (100-24) 76 in my table. I also want to visualize this in a chart so I can see  the development of the leave hours from an employee. Maybe I miss something but how do I create this?

[RemiAnthonise]

@Anonymous and other members of the community,

I don't want to be rude, but if you have some time and any ideas / suggestions regarding my latest post(s), please! I'm a bit stuck now so I could really use your help.

[Anonymous]

@RemiAnthonise,

Please create a measure using DAX below and check if you get expected result.

Leave = CALCULATE(SUM(JournalTrans[LeaveHours])-SUM(JournalTrans[Hours]),FILTER(JournalTrans,JournalTrans[Category]="A-verlof"))

Regarding to your second question, please share sample data of your table and post expected result based on the sample data.

Regards,
Lydia

[RemiAnthonise]

Thanks, Lydia. It seems this did the trick!

Regarding my second question: I hope I'll be able to post this by the end of the week. I'll let you guys know. 

[RemiAnthonise]

Below image is a simple display of what I would like to create.

In your previous comment you gave me this solution:

Leave = CALCULATE(SUM(JournalTrans[LeaveHours])-SUM(JournalTrans[Hours]),FILTER(JournalTrans,JournalTrans[Category]="A-verlof"))

I want to display the outcome of this formula in a chart, so in my example 92 / 84 / 76 etc.

[RemiAnthonise]

I have the following sample data:

Leavehours = 100

LeaveTest = CALCULATE(SUM(JournalTrans[LeaveHours])-SUM(JournalTrans[Hours]);FILTER(JournalTrans;JournalTrans[Category]="rem100"))

LeaveTest2 = CALCULATE(SUM(JournalTrans[LeaveHours])-SUM(JournalTrans[Hours]);FILTER(JournalTrans;JournalTrans[Category]="d100"))

It isn't clear to me why I get these results. 

Leavehours = 100 makes sense because I gave it this value. But what about LeaveTest and LeaveTest2?

Besides that, obviously this isn't the result I expected. I don't want one (or 2, because I have 2 columns) constant value but I want it to calculate it each row. See my previous post for my desired values. 

Do you have any idea? I can imagine it has something to do with the Leavehours = 100 in my DAX formula. I know it isn't possible with DAX but I need to do something with PREVIOUS (row).

[Anonymous]

@RemiAnthonise,

Please post expected result based on the above sample data. In addition, could you please upload the sample data into Excel file so that I can copy and test?

Regards,
Lydia

[RemiAnthonise]

@Anonymous

Since I couldn't find an option to attach the file to this forum I've uploaded the file to our Sharepoint here.

What I expect as a result:

For example the following DAX:

LeaveTest =

CALCULATE(SUM(JournalTrans[LeaveHours]) (100)

-

SUM(JournalTrans[Hours]);

FILTER(JournalTrans;JournalTrans[Category]="rem100")) (5 and 10 = 15 )

Result: 85.

In the end, I want to display this data in a chart, like the image in my previous post.

[Anonymous · Accepted solution]

@RemiAnthonise,

Please check if the DAX in the following PBIX file return your expected result.

https://1drv.ms/u/s!AhsotbnGu1Nok2uVMZAq7F37JF7y

Regards,
Lydia

[RemiAnthonise]

@Anonymous Yes yes yes, this did the trick!

Thank you very much for your time and effort, Lydia.