Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Have an issue where I have the below measure:
YTD_Store_Open =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]),
CALCULATE(SUM('ERP Sales'[Sales]),
FILTER(ALL('Date Dimension')
,'Date Dimension'[Date] >= [Store_MinDate])
)
)
)
This works as intended except when totalling, because the date filter for [Store_MinDate] looks over the entire data set rather then on a by store basis. Hence I implemented a SUMX to do a row by row calc to let the totals work correctly.
YTD_Store_Open_CompOnly =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0)
)
Now when I have no Month or date selected, this works as intended, however I cannot fix my measures in that when a user selects a month it filters measure 'YTD_Store_Open_CompOnly ' even though I don't want it to (the measure YTD_Store_Open works as intended excetp for totals)
How can I update my SUMX or use a similar measure so that I can calculate the SUM TOTALS on a row by row basis but ignore a date filter selection.
Note: I have tried using ALL('Date Dimension') in the second measure as well as the same filter from my first expression, i.e.
YTD_Store_Open_CompOnly =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0),
ALL('Date Dimension')
OR
YTD_Store_Open_CompOnly =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0),
FILTER(ALL('Date Dimension')
,'Date Dimension'[Date] >= [Store_MinDate])
)
)
Thanks
Solved! Go to Solution.
Hi @Anonymous,
Please try the measure below. Please check out your Message for details.
YTD_Summarize = SUMX ( SUMMARIZE ( 'Store', [P], [S], "Value", [YTD_Store_Open] ), [Value] )
Best Regards,
Dale
Hello, i'm strugglig with the same problem, and the proposed solution doesn't work for me. Any idea?
thanks!
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @Anonymous,
Can you share the file? DAX formula responds to the context. So it hard to trouble shoot with a bare formula. Did you try to recognize the Total context with "Hasonevalue"?
Best Regards,
Dale
Hi @Anonymous,
Please try the measure below. Please check out your Message for details.
YTD_Summarize = SUMX ( SUMMARIZE ( 'Store', [P], [S], "Value", [YTD_Store_Open] ), [Value] )
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |