Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Vladimir_NBG
Helper I
Helper I

Creating DAX formula for group of products

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.

 

Vladimir_NBG_0-1730293736309.png

 

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

 

Vladimir_NBG_1-1730293896637.png

 

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.

2 ACCEPTED SOLUTIONS

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.

 

View solution in original post

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

View solution in original post

22 REPLIES 22
johnt75
Super User
Super User

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 NAMEPRODUCT NAMEMONTHCUSTOMER NAMESALES IN EUR
JOHNPRODUCT AJan-25CUSTOMER 1100
JOHNPRODUCT BJan-25CUSTOMER 150
FRANKPRODUCT CJan-25CUSTOMER 589
LISAPRODUCT AJan-25CUSTOMER 390
JOHNPRODUCT CJan-25CUSTOMER 1100
NIKPRODUCT AJan-25CUSTOMER 465
FRANKPRODUCT BFeb-25CUSTOMER 5130
FRANKPRODUCT AFeb-25CUSTOMER 175
NIKPRODUCT BFeb-25CUSTOMER 475
JOHNPRODUCT GFeb-25CUSTOMER 823
LISAPRODUCT BFeb-25CUSTOMER 325
LISAPRODUCT DMar-25CUSTOMER 435
LISAPRODUCT CMar-25CUSTOMER 376
FRANKPRODUCT AMar-25CUSTOMER 530
NIKPRODUCT CMar-25CUSTOMER 421
NIKPRODUCT AMar-25CUSTOMER 234
NIKPRODUCT BMar-25CUSTOMER 4-75

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

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

This is what you sent me in PBIX file:

Vladimir_NBG_0-1730372846473.png

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).

 

Vladimir_NBG_1-1730373030436.png

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.

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

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:

 

Vladimir_NBG_0-1730387525480.png

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 NAMEPRODUCT NAMECUSTOMER NAMESALES IN EUR PRODUCT NAMEPRODUCT NAME CODE
JOHNMEATCUSTOMER 1100 MEATPRODUCT A
JOHNPHISHCUSTOMER 150 PHISHPRODUCT B
FRANKOILCUSTOMER 589 OILPRODUCT C
LISAMEATCUSTOMER 390 BEERPRODUCT D
JOHNOILCUSTOMER 1100 WATERPRODUCT G
NIKMEATCUSTOMER 465   
FRANKPHISHCUSTOMER 5130   
FRANKMEATCUSTOMER 175   
NIKPHISHCUSTOMER 475   
JOHNWATERCUSTOMER 823   
LISAPHISHCUSTOMER 325   
LISABEERCUSTOMER 435   
LISAOILCUSTOMER 376   
FRANKMEATCUSTOMER 530   
NIKOILCUSTOMER 421   
NIKMEATCUSTOMER 234   
NIKPHISHCUSTOMER 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 think this is right now. You need to add a filter on product name code to the calculation item.

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

 

Vladimir_NBG_0-1730448930273.png

 

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.

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!!!!!!!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.