Forum Discussion

elemesey's avatar
elemesey
Frequent Visitor
2 years ago
Solved

Filtered Sales in Customer master without using 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
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi elemesey ,

    I updated your sample pbix file(see the attachment), please check if that is what you want.

    1. Delete the relationship between the table 'Fiscal Year based on Sales' and 'Sales'

    2. Create two measures as below 

     

    Measure = 
VAR _cust =
    SELECTEDVALUE ( 'Allcustomer'[Customer] )
VAR _fyears =
    ALLSELECTED ( 'Fiscal Year based on Sales'[Fiscal Year 2] )
VAR _fmonths =
    ALLSELECTED ( 'Fiscal Year based on Sales'[Fiscal Month] )
VAR _itemgroups =
    ALLSELECTED ( Sales[Item Group] )
VAR _dates =
    CALCULATETABLE (
        VALUES ( 'Fiscal Year based on Sales'[Date] ),
        FILTER (
            'Fiscal Year based on Sales',
            'Fiscal Year based on Sales'[Fiscal Year 2]
                IN _fyears
                    && 'Fiscal Year based on Sales'[Fiscal Month] IN _fmonths
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Customer] = _cust
                && 'Sales'[Date]
                IN _dates
                    && 'Sales'[Item Group] IN _itemgroups
        )
    )
    Sum of sales = SUMX(VALUES('Allcustomer'[Customer]),[Measure])

     

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi elemesey ,

    I updated your sample pbix file(see the attachment), please check if that is what you want.

    1. Delete the relationship between the table 'Fiscal Year based on Sales' and 'Sales'

    2. Create two measures as below 

     

    Measure = 
VAR _cust =
    SELECTEDVALUE ( 'Allcustomer'[Customer] )
VAR _fyears =
    ALLSELECTED ( 'Fiscal Year based on Sales'[Fiscal Year 2] )
VAR _fmonths =
    ALLSELECTED ( 'Fiscal Year based on Sales'[Fiscal Month] )
VAR _itemgroups =
    ALLSELECTED ( Sales[Item Group] )
VAR _dates =
    CALCULATETABLE (
        VALUES ( 'Fiscal Year based on Sales'[Date] ),
        FILTER (
            'Fiscal Year based on Sales',
            'Fiscal Year based on Sales'[Fiscal Year 2]
                IN _fyears
                    && 'Fiscal Year based on Sales'[Fiscal Month] IN _fmonths
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Sales' ),
            'Sales'[Customer] = _cust
                && 'Sales'[Date]
                IN _dates
                    && 'Sales'[Item Group] IN _itemgroups
        )
    )
    Sum of sales = SUMX(VALUES('Allcustomer'[Customer]),[Measure])

     

    Best Regards