cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 @v-rongtiep-msft  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
v-rongtiep-msft
Community Support
Community Support

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 @v-rongtiep-msft  for idea

 

 

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

 

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

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

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors