Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Zaoka900
Regular Visitor

Many to many relationship sum by product

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.

 

Screenshot_1.png

Screenshot_2.png

 

excel example

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

 

 

1 ACCEPTED SOLUTION

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] ) ))
        
            )
)

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi @Zaoka900 ,

Please check the relationship like the following.

vpollymsft_0-1662451128713.png

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] )
    )
)

vpollymsft_1-1662451240235.png

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

 

vpollymsft_2-1662451283779.png

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.

 

 

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

 

Screenshot_4.png

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] ) ))
        
            )
)

 

Anonymous
Not applicable

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.

Hi,

I tried your suggestion with Max, result:

 

Zaoka900_0-1662532219607.png

 

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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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,

Screenshot_3.png

 

 

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

 

or create a sales measure like

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors