Many to many relationship sum by product

[Zaoka900]

Hi, if someone can help me with this.

Basically I have 3 tables: import, sales, and product details connected with relationship.

I need to create measure that show sales by product in import table, so when I filter import date i see total sales by product and imports only for filtered date.

excel example

https://www.dropbox.com/s/vgtqao0yxnjg613/Import.Sales.xlsx?dl=0

[v-rongtiep-msft]

Hi @Zaoka900 ,

Please check the relationship like the following.

Then create 2 measures.

Meastest =
CALCULATE (
    SUM ( 'Import'[Value] ),
    FILTER (
        ALL ( 'Import' ),
        'Import'[SKU code] = SELECTEDVALUE ( 'SKU detail'[SKU code] )
    )
)

correct result =
CALCULATE (
    SUM ( Sales[Value] ),
    FILTER (
        ALL ( Sales ),
        Sales[SKU code] = SELECTEDVALUE ( 'SKU detail'[SKU code] )
    )
)

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zaoka900]

Because i'm trying to build this in Power Pivot - Data model, there si no function SELECTEDVALUE so i change code like this:

MasterTest=

CALCULATE (
    SUM ( 'Import'[Value] );
    FILTER (
        ALL ( 'Import' );
        'Import'[SKU code] = (IF ( HASONEVALUE ( Detail[SKU code] );VALUES ( Detail[SKU code] ) ))
    )
)

But result is blank cell, i dont... i tried  with and without relationship

[Zaoka900 · Accepted solution]

this code work, tnx @v-rongtiep-msft  for idea

=CALCULATE (
    SUM ( Sales[Value] );
    FILTER (
        ALL ( Sales );
        Sales[SKU code] = (IF ( HASONEVALUE ( 'Import'[SKU code]);VALUES ( 'Import'[SKU code] ) ))
        
            )
)

[v-rongtiep-msft]

Hi @Zaoka900 ,

You can change the formula.

CALCULATE (
    SUM ( 'Import'[Value] );
    FILTER (
        ALL ( 'Import' );
        'Import'[SKU code] = max(detail[sku])))

If I have misunderstood your meaning, please provide more details and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zaoka900]

Hi,

I tried your suggestion with Max, result:

not sure how to upload file, because i do all this directly in excel data model.

only dropbox link:

https://www.dropbox.com/s/vgtqao0yxnjg613/Import.Sales.xlsx?dl=0

[amitchandak]

@Zaoka900 , You also need to have a common date table

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

When I asked you to create common tables: https://youtu.be/a2CrqCA9geM

[Zaoka900]

OK, I created date dimension but not sure what to do now, result is same.

If needed i can change Sales Query by Group SKU without Date. I think i need formula in measure in Import that should somehow show total sales per SKU code

In reality i dont want to filter Sales Date, only Import Date.  Sales should always show total per SKU,

[amitchandak]

@Zaoka900 , 2 ways, do not join sales and date table

or create a sales measure like

calculate(sum(Sales[Value]), all('Date'))

[Zaoka900]

sorry Amit, result is same, it just repeat total sales for every SKU...

calculate(sum(Sales[Value]), all('Date'))

If you have time check on my dropbox link with example data.

Maybe i'm doing something wrong...