Measure totals (common problem but cannot solve)

[FatherTheWizard]

Hi 

The topic is quite common but could not solve this with the help of few topics. My goal is to have a measure which calculates client's monthly EBITDA for each month if the EBITDA for the whole filtered period is 0 or more-> this measures's name would be "EBITDA for profitable clients". Then I would have also the similar for unprofitable where the last "if" would be of course EBITDA < 0. I have the EBITDA measure in place which is detailed below in step 1.

1. I am using measure EBITDA which basically takes data from "Profit and loss table" where each row is on "grouping of" client and month and the sum columns are invoicing and costs. The EBITDA measure is simply invoicing - costs.

2. I have also measure "EBITDA for client" which sums the EBITDA for filtered period. 

EBITDA for client = CALCULATE([EBITDA];ALLEXCEPT('(dim) Date';'(dim) Date'[date])).

This works with "date" since my fact tables' timelines are on a monthly level.

3. Everything seems good at this point. Problem is I cannot create correctly, for example, the "EBITDA for unprofitable clients" since it does not show the sum level correctly at this point. I just made basic DAX which does not seem to be enough even though the client level seems to be working:

EBITDA for unprofitable client = CALCULATE(IF([EBITDA for client]<0;[EBITDA];0))

Any help?

[Ashish_Mathur]

Hi,

Not sure but try this

=EBITDA for unprofitable client = CALCULATE([EBITDA],FILTER(Data,[EBITDA]<0))

Replace Data with the name of your Table.

[FatherTheWizard]

Hi 

I actually noticed my "EBITDA for client" DAX is not working. My current DAX is the following but the problem is that it sums it only per client per month (data on monthly level) and I would like to have the total sum of the selected period. 

EBITDA for client = CALCULATE([EBITDA];ALLSELECTED('(dim) Date'[date]);ALLEXCEPT('(dim) Employee';'(dim) Employee'[employee_id]))

Can I use all selected somehow for the total selected period? Year is not enough.

[Ashish_Mathur]

Hi,

Your question is still not clear.  Share some data and show the expected result.

[FatherTheWizard]

Data sharing is unfortunately not possible. I can clarify this taking couple of steps back.

I have two fact tables which are used for EBITDA:

-Invoicing table (columns: client, employee, date, invoicing value) -> data on a monthly basis

-Cost table (columns: client, employee, date, costvalue) -> data on a monthly basis

EBITDA calculation is a simple DAX measure [Invoicing value] - [Cost value]. As you can see this is per client per employee per month due to data granularity.

-> I want to get dynamic list of clients which are EBITDA >= 0 and other list for client which are EBITDA <0 during the WHOLE time period chosen by user on the dashboard time slicer. If person chooses the timeline to be 1.10.2017-1.6.2018 then I would like to have each client's total EBITDA of that period (9 months).  

Example: A client's EBITDA for each month during 9-month period is Oct: -1 Nov: -1 Dec: -1 Jan: -1 Feb -1 Mar: +1 Apr: +1 May +1 Jun: +1 -> Total of -1. That would mean that client would be on list "EBITDA <0". And the total value of -1 would occur for that client for each month (selected). That way it would not count individual positive months but take a look only at the period total.

Did this clarify the wanted outcome?