# Measure that calculates the following per company: inventory - sales per local company?

• ### Question

• EDIT 2:

I think that solving the following question would solve my problem described in a very long text below:

I have a table with `SalesCompanies`. The table contains one column `ChildCompany` and another column `ParentCompany`.

How can I make the measure below aggregate per `ParentCompany` regardless of `ChildCompany`filtering?

``````CustomerSalesSinceLastInventory:=CALCULATE(
SUM(factCustomerSales[Quantity])
;FILTER(
ALL(dimCalendar[Date])
;dimCalendar[Date] >= MIN(parCurrentMonth[Date])
&& dimCalendar[Date] <= MAX(dimCalendar[Date])
)
)``````

End Edit 2

I am calculating inventory as inventory + purchase - sales. The problem is that inventory and purchase is per parent company and sales is per local company. I want the inventory measure to make a calculation per parent company.

I have a table called Customers that has the follosing columns

LocalCompany, ParentCompany, ...

PC A, PC A

LC A, PC A

LC B, PC A

PC B, PC B

LC C, PC B

LC D, PC B

LC E, PC B

LC F, PC B

The measure below only gives results per parent company, which is as intended:

```﻿﻿CustomerInventoryWithoutPurchaseForecastAndSalesForecast:=IF(
MAX(dimCalendar[Date]) < MIN(parCurrentMonth[Date])
;[CustomerInventoryCorrectBeforeCurrent]
;[CustomerInventoryLastActual]
)```

But then I add the following measure to it and the result becomes per local company:

```CustomerSalesSinceLastInventory:=CALCULATE(
SUM(factCustomerSales[Quantity])
;FILTER(
ALL(dimCalendar[Date])
;dimCalendar[Date] >= MIN(parCurrentMonth[Date])
&& dimCalendar[Date] <= MAX(dimCalendar[Date])
)
)```

How can I modify the measure CustomerSalesSinceLastInventory so that it only calculates a result for PC A and PC B. For PC A it should aggregate all sales from local companies where the parent company is PC A and for PC B it should aggregate all sales from local companies where the parent company is PC B.

The image shows how the report looks. It is inventory (I) that I am not able to produce.

EDIT:

I think I need a measure similar to:

`CustomerSalesSinceLastInventory:=if(SalesCompanies[LocalCompany]=SalesCompanies[ParentCompany];FILTER(SalesCompanies[LocalCompany]=SalesCompanies[ParentCompany]); SUM ALL)`

Maybe I need to create some kind of nested filter because of how CustomerSalesSinceLastInventory is a pretty complex measure. I am only speculating. I haven't been able to solve this, but really need a solution.

Here is my best attempt so far:

`CustomerSalesSinceLastInventory:=CALCULATE(SUMX(factCustomerSales;CALCULATE(	SUM(factCustomerSales[Quantity])	;FILTER(		ALL(dimCalendar[Date])		;dimCalendar[Date] >= MIN(parCurrentMonth[Date])			&& dimCalendar[Date] <= MAX(dimCalendar[Date])	)));FILTER(ALL(dimSalesCompanies[SalesCompany]);dimSalesCompanies[SalesCompany]=dimSalesCompanies[SalesCompanyGroup]))`

EDIT2: Here are all the measures I am using currently that finally arrive at an inventory. The measures are wrong however, beccause sales is on localcompany/salescompany and inventory is on parentcompany/groupcompany

CustomerInventoryCorrectBeforeCurrent:=CALCULATE(
SUM(factCustomerInventory[Quantity])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] = MAX(dimCalendar[EndDateMonth])
)
)

CustomerInventoryLastActual:=CALCULATE(
SUM(factCustomerInventory[Quantity])
;FILTER(
ALL(dimCalendar)
;dimCalendar[Date] = MAX(parCurrentMonth[EndDatePriorMonth])
)
)

CustomerInventoryWithoutPurchaseForecastAndSalesForecast:=IF(
MAX(dimCalendar[Date]) < MIN(parCurrentMonth[Date])
;[CustomerInventoryCorrectBeforeCurrent]
;[CustomerInventoryLastActual]
)

CustomerSalesSinceLastInventory:=CALCULATE(
SUM(factCustomerSales[Quantity])
;FILTER(
ALL(dimCalendar[Date])
;dimCalendar[Date] >= MIN(parCurrentMonth[Date])
&& dimCalendar[Date] <= MAX(dimCalendar[Date])
)
)

CustomerPurchaseSinceLastInventory:=[SalesSinceLastInventory]

Customer I:=[CustomerInventoryWithoutPurchaseForecastAndSalesForecast] - [CustomerSalesSinceLastInventory] + [CustomerPurchaseSinceLastInventory]

• Edited by Monday, November 2, 2015 3:31 PM
Monday, November 2, 2015 11:26 AM

• Regarding your 'Edit2' (I didn't bother reading the rest of the post, sorry ;-)):

BTW, your filter dimCalendar[Date] <= MAX(dimCalendar[Date]) looks a bit trivial at first sight. You might replace the FILTER by

DATESBETWEEN(dimCalendar[Date],parCurrentMonth[Date],BLANK())

• Proposed as answer by Monday, November 16, 2015 9:07 AM
• Marked as answer by Wednesday, December 2, 2015 8:51 AM
Tuesday, November 3, 2015 8:49 AM

### All replies

• Did you form a relationship between your Sales and your LocalCompany/ParentCompany table? You will likely need to do some work to get that all related correctly given the limitations on Power Bi relationships.
Monday, November 2, 2015 6:31 PM
• Regarding your 'Edit2' (I didn't bother reading the rest of the post, sorry ;-)):