Creating DAX formula for group of products

[Vladimir_NBG]

Hi all,

I need to create formula related to group of products (3 products). Below is the dummy table I have, and I would like to create product group which consist of Product A, Product B, and Product C.

This is expected table I would like to make after formula is done:

Now, the point is: Headline in black "Total (all 3 products sold & value of all 3 products are positive) --> this means that this formula should take into account only if sales rep sold all 3 products to certain customer.

Example, in the choosen period of time (in my dummy table) Lisa managed to sell all 3 products to "Customer 3", and that's why in the last columns he has distribution of 1, and value of 191.

Is it possible to make this in this kind of table, to have all in one place?

Thank you.

[johnt75 · Accepted solution]

My mistake, I'd left the original [Sales Value] measure in the calculation group instead of [Sales Value Lookup] which uses the data from the two separate tables.

[johnt75]

I don't think that you need to check separately for if they sold a product and the value is positive. If the value is positive that must surely imply that they sold that product.

You can create a measure for the total value like

Sales Value All Products =
VAR ValidCustomers =
    FILTER (
        DISTINCT ( 'Table'[Customer Name] ),
        CALCULATE ( [Sales Value], 'Table'[Product Name] = "Product A" ) > 0
            && CALCULATE ( [Sales Value], 'Table'[Product Name] = "Product B" ) > 0
            && CALCULATE ( [Sales Value], 'Table'[Product Name] = "Product C" ) > 0
    )
VAR Result =
    CALCULATE ( [Sales Value], ValidCustomers )
RETURN
    Result

To return the number of customers, replicate the code and return COUNTROWS(ValidCustomers) instead.

[Vladimir_NBG]

Just tried, does not work

[johnt75]

Can you provide a PBIX, or some sample data along with the results you are expecting? I tried to grab the data from your initial picture using Excel's get data from picture, but it didn't like it.

[Vladimir_NBG]

uf, unforunately I am not able to share it. But basicaly I put this simple dummy database, and expected output I want to get is second table. You can consider that this what I sent is entire database

[johnt75]

Can you paste the dummy data as text ? I can't use the picture.

[Vladimir_NBG]

Here it is:

Database
SALES REP NAME	PRODUCT NAME	MONTH	CUSTOMER NAME	SALES IN EUR
JOHN	PRODUCT A	Jan-25	CUSTOMER 1	100
JOHN	PRODUCT B	Jan-25	CUSTOMER 1	50
FRANK	PRODUCT C	Jan-25	CUSTOMER 5	89
LISA	PRODUCT A	Jan-25	CUSTOMER 3	90
JOHN	PRODUCT C	Jan-25	CUSTOMER 1	100
NIK	PRODUCT A	Jan-25	CUSTOMER 4	65
FRANK	PRODUCT B	Feb-25	CUSTOMER 5	130
FRANK	PRODUCT A	Feb-25	CUSTOMER 1	75
NIK	PRODUCT B	Feb-25	CUSTOMER 4	75
JOHN	PRODUCT G	Feb-25	CUSTOMER 8	23
LISA	PRODUCT B	Feb-25	CUSTOMER 3	25
LISA	PRODUCT D	Mar-25	CUSTOMER 4	35
LISA	PRODUCT C	Mar-25	CUSTOMER 3	76
FRANK	PRODUCT A	Mar-25	CUSTOMER 5	30
NIK	PRODUCT C	Mar-25	CUSTOMER 4	21
NIK	PRODUCT A	Mar-25	CUSTOMER 2	34
NIK	PRODUCT B	Mar-25	CUSTOMER 4	-75

[johnt75]

Thanks. The code I gave produces the results you're expecting. See the attached PBIX.

[Vladimir_NBG]

Hi. unfortunately it's not the table I need.

This is what you sent me in PBIX file:

But this is only the last columns of the table i sent. I need also all columns that should go before (please see below columns that I miss).

So the point is, i need to have 1 table. I know how to make them separetely, but the point is I need them in 1 single table.

Hope you will manage.

Thank you once again.

[johnt75]

You can achieve that with a calculation group. Create calculation items for each product, the total and the total for all products.

[Vladimir_NBG]

Hi. It works!!!! You are the man!

Sorry, just one more thing. I make a mistake. Actually, this "product name" was not in the database. In the databas I had actual product, and product names (A,B,C,D,G) are actually in the other, lookup table. Please see below:

So it means that formula needs to be adjusted, instead of product name (meat, phish etc.) to be shown product name code (A,BC) - but as I said, it is in different table which is connected to database. I tried by myself by simply replacing this part in your formula, but without success.

Are you able to adopt the formula?

Let me know if I need to close this request and to open another one, no problem.

THANK YOU!!!!

[johnt75]

Create a one-to-many relationship from your product lookup table to the database table, and then change the calculation items to reference the product name column in the lookup table. That should just work then.

[Vladimir_NBG]

I tried, but without success, I am getting wrong totals.

Can you please make it?

Here is both tables below:

Database		PRODUCT LOOKUP TABLE
SALES REP NAME	PRODUCT NAME	CUSTOMER NAME	SALES IN EUR		PRODUCT NAME	PRODUCT NAME CODE
JOHN	MEAT	CUSTOMER 1	100		MEAT	PRODUCT A
JOHN	PHISH	CUSTOMER 1	50		PHISH	PRODUCT B
FRANK	OIL	CUSTOMER 5	89		OIL	PRODUCT C
LISA	MEAT	CUSTOMER 3	90		BEER	PRODUCT D
JOHN	OIL	CUSTOMER 1	100		WATER	PRODUCT G
NIK	MEAT	CUSTOMER 4	65
FRANK	PHISH	CUSTOMER 5	130
FRANK	MEAT	CUSTOMER 1	75
NIK	PHISH	CUSTOMER 4	75
JOHN	WATER	CUSTOMER 8	23
LISA	PHISH	CUSTOMER 3	25
LISA	BEER	CUSTOMER 4	35
LISA	OIL	CUSTOMER 3	76
FRANK	MEAT	CUSTOMER 5	30
NIK	OIL	CUSTOMER 4	21
NIK	MEAT	CUSTOMER 2	34
NIK	PHISH	CUSTOMER 4	-75

[johnt75]

For the total where they bought all products, do you just want to see the totals for the valid customers for products A, B and C. Or do you want to include values for D and G as well ?

[Vladimir_NBG]

Only A, B, C. Like you did first time, exactly the same. 

[johnt75]

I think this is right now. You need to add a filter on product name code to the calculation item.

[Vladimir_NBG]

I checked, but the total is now different for Total All products (A, B, C). Please look your file you sent me:

[johnt75]

They are coming from different data. The top table is coming from the initial data you posted, with the product names, the second table is coming from the later data using the lookup table.

[Vladimir_NBG]

Hi. I know. But that's the problem. The result should be the same - meaning, in the second table (last one), I should get same result like you put initially, in the first table.

I am not sure why you wrote "they are coming from different data"? Database is the same (same sales values), only we added this element of lookup table.

[johnt75 · Accepted solution]

My mistake, I'd left the original [Sales Value] measure in the calculation group instead of [Sales Value Lookup] which uses the data from the two separate tables.

[Vladimir_NBG · Accepted solution]

It works! That's it. THANK YOU VERY MUCH!!!!!!!