Forum Discussion

elemesey's avatar
elemesey
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 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! 😊

 

Need Help

4 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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's avatar
      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's avatar
        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.