Forum Discussion

Frequent Visitor

2 years ago

Calculate Column based on another table without relationship

Hi guys, I'm beginner in Power BI and need help. I need a DAX column with my customer list that will show their sales in the slicers based on the year and item group selected from the sales dat...

Super User

2 years ago

Try this

Sum of Sales2 = 
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        Sales,
        Sales[Date]
            IN VALUES ( 'Fiscal Year based on Sales'[Date] )
                && Sales[Customer]
                    IN VALUES ( Allcustomer[Customer] )
                        && Sales[Region] IN VALUES ( Allcustomer[Region] )
    )
)

You can add or rmove IN filters as needed.

elemesey
Frequent Visitor
2 years ago
Hi danextian ,

I greatly appreciate your quick help 😊

Unfortunately, it didn't give my desired result; perhaps I did not explain clearly. The DAX formula you provided is the total of all customers, whether filtered or not. . Please see below images for your reference.

No Filter:

With Filter:

What I want is the sales will be reflected based on what year I selected, as well in the item group.

If you could continue to assist me, I would be grateful 🙂

Thank you.
- danextian
  Super User
  2 years ago
  The formula should be correct. Please make sure you're using the correct columns
  
  Please see attached pbix for your reference.
  
  PS: this would have been simple had you used relationships.
  Customer count - Test.pbix107 KB
  - elemesey
    Frequent Visitor
    2 years ago
    Thank you danextian. Yeah the DAX works now using the sales table 🙂
    
    But is this doable in the Customer Master table? In my main file, I need a DAX that displays the sales scenario for my Customer master. I know relationship can simple do this, however I need a DAX formula because relationship is no longer applicable in my main file.
    
    I would appreciate it if you could continue to assist me 🙂