Hi all,
I'm a PowerBI newbie and sitting here in front of my laptop, struggling to find the right way aggregating my data. I hope that maybe you can help me, that would be awesome.
My goal is to make a cross-sell analysis. In order to show you my problem I created the example table you can see below.
Problem 1: What is missing until now, is the column "Sum by customer" which I need to create with a DAX-formula, but I don't know how. The idea of the formula "Sum by customer" explained by the second row for customer A with a sum of 2: the formula should sum all values in the columns "Product 1" - "Product 5" for customer A in all rows where the customer is A (here it is: 1 + (-1) + 1 +1 = 2). How is this formula going to look like?
| Customer | Product 1 | Product 2 | Product 3 | Product 4 | Product 5 | Sum by contract | Sum by customer |
A | 1 | | | | | 1 | 2 |
| B | | 1 | | | | 1 | 0 |
| C | | | | 1 | | 1 | 1 |
| A | -1 | | | | | -1 | 2 |
| A | | | 1 | | | 1 | 2 |
| B | | -1 | | | | -1 | 0 |
| A | | | | | 1 | 1 | 2 |
Problem 2: Having my table above, I need a measure which gives me back the number of customers which have a value in "Sum by customer" equal or greater than 2. For this example the formula should give me back a value of 1, with "A" being the only customer with >= 2 contracts.
Help from you, and if it's only for either for one of the problems, would be highly appreciated! Thanks in advance 😊
Felix
