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
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
Solved! Go to 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] ) ))
)
)
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.
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
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] ) ))
)
)
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:
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
@Zaoka900 , You also need to have a common date table
When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
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,
@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...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |