count and sum values filter by many columns

[lulufly]

Hello,

I have the table bellow with date, order number, value and 5 columns for the 5 promotions that i can use an each order.

table

What is the best way to get total orders by promotions and the quantity orders by promotion.

Regard that i can have the same promotion in difierent column buy never same promotion on the same order (Row)

Thaks for all

Regards

Luis

[Greg_Deckler]

I would unpivot your promotion columns.

[lulufly]

Hu Greg,

I tried that, but with that i got duplicates orders values.

Sorry, im really bad with pivot and unpivot ;(

thanks  

[Anonymous]

What does it mean that an order has several promotions attached to it?

Best
D

[lulufly]

Means that for each order we can have up to 5 promotions attached.

Ex:

Order 10, can have, 10% discount for the 1st purchase, 10% birthday month, Free shipping and 15% discount with salesman code

Order 20, can have free Shipping and 10% discont for the fisrt purchase

Order 30, can have 10% birthday discount

Order 40, can have 15% discount with salesman code

....

regards

Luis

[Anonymous]

I gather they are cumulative?

If the first discount is 10% and the second 20%, then the total discount is 30%? IS that correct?

Best
D

[Anonymous]

We need data in a text form to quickly paste it into PBI. A picture won't do in this case.

But the solution to this is either 1) unpivot the table so that the name of the promotion becomes a value in a column and then do DISTINCTCOUNT on order id or 2) create a many-to-many relationship between unique orders and unique promotions. For this you'll need a bridge table that'll link orderid and promotionid (promotion name).

Best
D

[Anonymous]

Here's a solution attached that uses the Many-To-Many technique. Please analyze this thoroughly so that you understand it. I've put a note in the report that explains why it's working the way it does. If you want to know more about Many-To-Many, then please go to www.sqlbi.com and search for articles on it by typing in the Search Box "many to many."

Best

D

[Anonymous]

And here's a solution that is simpler than the previous one but requires you to indicate orders with no promotions as well. But the advantage is you just have 2 measures that work IN ALL CIRCUMSTANCES.

Best

D