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.
Hello!
I want the code to sum the sales of the End Users ( 'DB'[End User]) that sold <25000 within a Quarter, Product Group and Country.
I have the following code:
Small Sales =
SUMX (
GROUPBY (
'DB',
'DB'[Year],
'DB'[Quarter],
'DB'[Product Group],
'DB'[Country],
'DB'[Stock],
'DB'[End User]
),
IF ( 'DB'[Stock] <> "Not Stock" || [Sales] > 25000, [Sales] )
)
The problem is that when I put that in a Matrix...:
The sum of the individual week totals (33M) is greater than the Total (27,8M) in the matrix.
I believe is because it is on a weekly filter context, so it won't evaluate if QUARTERLY SALES are <25000, but if WEEKLY SALES are <25000
How do I fix it?
I need to display a weekly value of the Small Sales, but the code needs to use all the weeks in the quarter to determine if sales are <25000 or not
Please help!
Thank you very much!
Solved! Go to Solution.
This doesn't seem like it should work (even after correcting the various syntax errors) since _sales is a constant variable.
Maybe we can keep the calculate inside the FILTER like this?
CALCULATE (
[Sales],
FILTER (
'DB',
'DB'[Stock] <> "Not Stock" ||
CALCULATE (
[Sales],
ALLEXCEPT (
'DB',
'DB'[Year],
'DB'[Quarter],
'DB'[Product Group],
'DB'[Country],
'DB'[Stock],
'DB'[End User]
)
) < 25000
)
)
@Anonymous , try measure like
Var _sales = calculate([Sales], filter(allselected('DB'), 'DB'[Year] = max('DB'[Year]) && 'DB'[Quarter] max('DB'[Quarter]) &&
'DB'[Product Group] =max('DB'[Product Group]) && 'DB'[Country] = max('DB'[Country]) && DB'[Stock] =max(DB'[Stock]) && 'DB'[End User] =max('DB'[End User]))
return
calculate([Sales], filter('DB', 'DB'[Stock] <> "Not Stock" || _sales >25000 ) )
This doesn't seem like it should work (even after correcting the various syntax errors) since _sales is a constant variable.
Maybe we can keep the calculate inside the FILTER like this?
CALCULATE (
[Sales],
FILTER (
'DB',
'DB'[Stock] <> "Not Stock" ||
CALCULATE (
[Sales],
ALLEXCEPT (
'DB',
'DB'[Year],
'DB'[Quarter],
'DB'[Product Group],
'DB'[Country],
'DB'[Stock],
'DB'[End User]
)
) < 25000
)
)
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |