Filter and sum a second colunmn by filter context

Question

I have a table with a CompanyID, PartnerCompanyID and Sales. If the customer is a group partner company, a further PartnerCompanyID column is filled with the CompanyID of the internal customer.

I’d like to sum the sales but only for sales other than internal sales between the companies within the filter context. That is the sales with no (empty) PartnerCompanyIDs or with PartnerCompanyIDs other than the CompanyID’s within the filter context.

Model

I tried with

SalesExtern = CALCULATE(SUM(Sales[Sales]);Filter(Sales;NOT(CONTAINS(ALLSELECTED(Company[CompanyID]);Company[CompanyID];LASTNONBLANK(Sales[PartnerCompanyID];"")))))

The sum seems to be ok, but the values per line are wrong:

Model2

PBIX File

thanks a lot :)

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-02T00:06:07

Why not something much simpler like this?

SalesExtern = CALCULATE(SUM(Sales[Sales]);
                  FILTER(Sales; ISBLANK(Sales[PartnerCompanyID])))

Edit: Try this instead.

Create a duplicate table of Company using New Table under Modeling:

PartnerCompany = Company

Create a relationship from PartnerCompany[CompanyID] to Sales[ParterCompanyID].

Define your new measure as follows:

ExternalSales = 
    VAR SelectedClusters = CALCULATETABLE(VALUES(Company[Cluster]), ALLSELECTED(Sales))
    RETURN CALCULATE(SUM(Sales[Sales]), NOT(PartnerCompany[Cluster] IN SelectedClusters))

Filter and sum a second colunmn by filter context

1 Answers