Calculate Column based on another table without relationship

[elemesey]

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 data without the use of a relationship.

This is the sample result I'm looking for:

Test File - https://drive.google.com/file/d/10VLWUhmGCSoHUxvK4yeiR77mKlbNMBcA/view?usp=drive_link 

Thank you in advance, and I appreciate everyone! 😊

[danextian]

Hi @elemesey ,

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]

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]

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.

[elemesey]

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 🙂