Forum Discussion
Helper ICreating DAX formula for group of products
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.
It works! That's it. THANK YOU VERY MUCH!!!!!!!
Super UserYou can achieve that with a calculation group. Create calculation items for each product, the total and the total for all products.
Helper IHi. 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.
- 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 ?
- johnt75
I think this is right now. You need to add a filter on product name code to the calculation item.
- johnt75
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
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 - Vladimir_NBG
Only A, B, C. Like you did first time, exactly the same.
- Vladimir_NBG
I will check it, just to be sure, i will check it in my database. I will let you know. Thanks!
- 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.
- Vladimir_NBG
It works! That's it. THANK YOU VERY MUCH!!!!!!!