Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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.
Just tried, does not work
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.
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
Can you paste the dummy data as text ? I can't use the picture.
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 |
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.
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!!!!
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.
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 |
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 ?
Only A, B, C. Like you did first time, exactly the same.
I checked, but the total is now different for Total All products (A, B, C). Please look your file you sent me:
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.
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.
It works! That's it. THANK YOU VERY MUCH!!!!!!!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |