Reply
elemesey
Frequent Visitor

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

elemesey_0-1695192637211.png




This is the sample result I'm looking for:

elemesey_1-1695192787174.png


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


Thank you in advance, and I appreciate everyone! 😊

 

4 REPLIES 4
danextian
Super User
Super User

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.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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:

elemesey_3-1695196100467.png

 

With Filter:

 

elemesey_5-1695196243957.png

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

elemesey_2-1695196060816.png

 

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

Thank you.


The formula should be correct. Please make sure you're using the correct columns

danextian_0-1695202528610.png

Please see attached pbix for your reference.

PS: this would have been simple had you used relationships.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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 🙂

avatar user

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!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)