topic Re: Many to many relationship sum by product in DAX Commands and Tips

Many to many relationship sum by product

Zaoka900 — Mon, 05 Sep 2022 13:53:24 GMT

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

Re: Many to many relationship sum by product

amitchandak — Mon, 05 Sep 2022 14:34:03 GMT

@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-solution-8e3eccb41bda

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

Re: Many to many relationship sum by product

Zaoka900 — Mon, 05 Sep 2022 15:14:59 GMT

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,

Re: Many to many relationship sum by product

amitchandak — Mon, 05 Sep 2022 15:56:35 GMT

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

or create a sales measure like

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

Re: Many to many relationship sum by product

Zaoka900 — Mon, 05 Sep 2022 18:33:00 GMT

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...

Re: Many to many relationship sum by product

Anonymous — Tue, 06 Sep 2022 08:02:05 GMT

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.

Re: Many to many relationship sum by product

Zaoka900 — Tue, 06 Sep 2022 21:37:26 GMT

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

Re: Many to many relationship sum by product

Anonymous — Wed, 07 Sep 2022 03:14:10 GMT

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.

Re: Many to many relationship sum by product

Zaoka900 — Wed, 07 Sep 2022 06:35:02 GMT

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

Re: Many to many relationship sum by product

Zaoka900 — Wed, 07 Sep 2022 15:05:08 GMT

this code work, tnx @Anonymous for idea

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