[HELP] Many to many replationship Sum ignore filter

[minhvuong93]

 Hi all,

I have a scenario like this:

Table 1: Saleman Visit (Beat) Plan: Each Saleman is assigned to visit a group of Customers. (One Saleman can be assigned visit the same Customer !)

Table 2:  

Actual Sale Quantity made by each Saleman to a Customer.

In this case, the saleman can sell to any customer (even if he is NOT assigned to visit this customer (Unplanned Visit).

Desired Report (Output):

Basically I want to use the layout like Table 1, but add two column:

 1 - Sale Qty made by the Saleman to the customer

 2- Sale Qty total by the Customer ID (regardless of if they have the plan or not)

Since I dont have a unique value to create relationship with this case, how to achieve the desire ouput?

Creating another table of Customer ID is undesirable since I have 1,000,000 rows per table

[Phil_Seamark]

HI @minhvuong93

This calcuated table might be getting close.

Let me know what you think

New Table = SUMMARIZECOLUMNS(
                'Actual'[Salesman],
                'Actual'[Customer ID],
                "Quantity Sold by Salesman" , SUM(Actual[Quantity]),
                "Quantity total by Customer ID" , CALCULATE(SUM('Actual'[Quantity]),ALLEXCEPT('Actual',Actual[Customer ID]))
                )

[minhvuong93]

Thanks @Phil_Seamarkfor the suggestion, actually I am doing this on Power Pivot since Power Bi having limited capability to export report to excel..

[v-ljerr-msft]

Hi @minhvuong93,

According to your description above, you should be able to use the formulas below to add two column to the "Plan" table in your scenario.

Quantity sold by Saleman =
LOOKUPVALUE (
    Actual[Quantity],
    Actual[Saleman], Plan[Saleman],
    Actual[Customer ID], Plan[Customer ID]
)
    + 0

Quantity tatal by Customer ID =
VAR customerId = Plan[Customer ID]
RETURN
    CALCULATE ( SUM ( Actual[Quantity] ), Actual[Customer ID] = customerId )
        + 0

Regards

[minhvuong93]

@v-ljerr-msft Hi there,

Thank you for ur answer. But I cannot add the formula "VAR" ....it has to be VAR.S VAR.P or something???

[v-ljerr-msft]

Hi @minhvuong93,

Never mind the intellisense(VAR.S, VAR.P, etc), just input VAR and finish the formula, it should work anyway.

Regards

[minhvuong93]

@v-ljerr-msft I just googled and found out that VAR no longer supported in Excel 2016?

I tried but cannot put the formula into a new column. they do not understand Var and Return as a formula

[v-ljerr-msft]

Hi @minhvuong93,

According to this article, it should be supported. And I'm using it within Power Pivot in Excel 2016. A little weird.

This feature is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only. Information provided here is subject to change.

---

As I am not very familiar with Power Pivot, I would suggest that you also post it on Power Pivot forum for better assistance on this issue.

Regards

[minhvuong93]

@v-ljerr-msft Did you create any relationship in order for this formula to work>

[v-ljerr-msft]

Hi @minhvuong93,

No, I didn't. Here is the Excel file I worked on for your reference.

Regards

[marcorusso]

Where did you read that VAR is not supported in Excel 2016?

However, you can try using EARLIER if you don't have VAR:

Quantity tatal by Customer ID =
    CALCULATE ( SUM ( Actual[Quantity] ), Actual[Customer ID] = EARLIER ( Plan[Customer ID] ) )
        + 0

[minhvuong93]

@marcorusso 

Thank you for replying. Actually I did make the Var function work in a calculated column, however when I return to the pivot table interface, it took about 30min or sth to load the data. I  guess using VAR is too complicated for this case.

In reply to your suggestion, is this a measures or do I have to insert another column into the data table (which consists of million of rows and related information....)

[minhvuong93]

Thank you @v-ljerr-msft , I already now can produce the same column like you suggest.

However, when I come back to my Pivot table,It took forever to load 😞

My data is a long list of customer transactions (~100,000 rows)

Is there anyway it can be a calculated measures?