How to see Bulk customers with my PowerPivot?

[BeepBeep]

Hi,

I have two questions.

In my fact table, I have some Invoices with many items tagged to it, I mean many items bought on the same invoice. I want to see those customers who bought more than one item on any given Invoice.

I have one Million transactions and my little system is dying when I apply filters in PowerPivot Table.

What is that measure or calculated column that I should apply in this situation?

Second question:

I have dim table named Targets with following columns.

YearlyGoal, Prize
75000, 5000
85000, 7000
95000, 8000

I want to see which Salesman is eligible for the Prize in which year? Obviously, I can get any Salesman's sales year wise but How to check whether somebody got eligible for a Prize comparing with the YearlyGoal?

BeepBeep
Thanks for your kind help.

 

[BeepBeep]

Sorry, forgot to mention..

This Target table does not have any relationships with any other table. That is where I am puzzled.
How to compare any Salesman's yearly Sales qty with this YearlyGoal qty to get the Prize?
I have following columns in my Fact table.

InvoiceNo, OrderDate, OrderTime, ShipDate, FreghtKey, SalesmanKey, ProductKey, StoreID, SalesQty

Please ask me for any other info needed.
Thanks
BeepBeep

 

[scottsen]

Your fact table looks pretty good -- typically you hit most perf issues by having too many columns, but that all look pretty required

Generally, I like to avoid adding a calc column to a fact table, but... I think you will be fine here... since there are only 2 distinct values in the column (so... it will compress quite well). It would perform slightly better if you do this in the data source or power query, but... probably not a massive difference.

Anyway, add a calc column: HasMultiProducts =CALCULATE(COUNTROWS(Invoices), ALL(Invoices), Invoices[InvoiceNo] = EARLIER(Invoices[InvoiceNo])) > 1

[I didnt type this into power pivot, so may have minor syntax errors ]

This will allow you to easily filter invoices down to just those with multiple lines.

 

[BeepBeep]

Scottsen,

I have no luck with the formula. I already tried something similar. At the same time this is very interesting for me.
But I doubt the combination of CALCULATE and COUNTROWS is the culprit for the error.

Any guess on my second question?
BTW I am using Office 2010, so I am not using PowerQuery.

 

[scottsen]

What kind of badness happens? on the calculate countrows?

Assuming you had Salesman on Rows, somethign like this would work?
Prize := CALCULATE(MAX(Targets[Prize]), FILTER(Targets, Targets[YearlyGoal] >= [Total Sales]))

 

[BeepBeep]

As I said the above two tables were not connected thru any relationship.

In fact I do not know how to connect them both because I have no logical means to connect them. Will the above formula works even if there's no relationship?