locked
Measure that calculates the following per company: inventory - sales per local company? RRS feed

  • 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 ChildCompanyfiltering?

    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 JP3O Monday, November 2, 2015 3:31 PM
    Monday, November 2, 2015 11:26 AM

Answers

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

    just add ALL(SalesCompanies[ChildCompany] as a filter to your CALCULATE statement.

    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 Charlie Liao Monday, November 16, 2015 9:07 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Tuesday, November 3, 2015 8:49 AM
    Answerer

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

    just add ALL(SalesCompanies[ChildCompany] as a filter to your CALCULATE statement.

    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 Charlie Liao Monday, November 16, 2015 9:07 AM
    • Marked as answer by Charlie Liao Wednesday, December 2, 2015 8:51 AM
    Tuesday, November 3, 2015 8:49 AM
    Answerer