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 PowerBi Guru's!
I have created a calculated column for later use in a different measure. However, I quickly came to a conclusion that this calculated column does not change when a filter is applied (a manager is selected in my situation). Here is my calculated column which sums up the sales for a month each year for a certain product (I have many products, many months and many years):
Total value by product Monthly =
var product = [Products]
var monthno = [Month No]
var yearno = [Year No]
return
CALCULATE(SUM(SALES[Sales in units]), filter(sales, [Products] = product), FILTER(sales, [Month No] = monthno), filter(sales, [Year No] = yearno))
The 'sales' table represents all unique invoices and dates.
Is there a way for me to translate this dax expression to be used as a measure and display the same values, but when I select a manager (not all products of the same type are sold by one manager) the values adapt to display a correct sum of sales in units monthly next to the product.
P.S. if you have some kind of tutorial that explains this phenomenon then please send it . I still do not understand fully how to make measures for certain totals.
Huge thanks for spending your time on my seemingly simple problem,
Zenons Belskis
Solved! Go to Solution.
@zenonsbelskiss , my Bad. Check
Total value by product Monthly =
var product = max(Table[Products])
var monthno = max(Table[Month No])
var yearno = max(Table[Year No])
return
CALCULATE(SUM(SALES[Sales in units]), filter(allselected(sales), [Products] = product && [Month No] = monthno && [Year No] = yearno))
@zenonsbelskiss , Try a measure like
Total value by product Monthly =
var product = max(Table[Products])
var monthno = max(Table[Month No])
var yearno = max(Table[Year No])
return
CALCULATE(SUM(SALES[Sales in units]), filter(sales, [Products] = product && [Month No] = monthno && [Year No] = yearno))
@amitchandak The measure that you suggested (I renamed it a sales in units total by product monthly) just displays the same values as just sales in units, not the sum. 😞
@zenonsbelskiss , my Bad. Check
Total value by product Monthly =
var product = max(Table[Products])
var monthno = max(Table[Month No])
var yearno = max(Table[Year No])
return
CALCULATE(SUM(SALES[Sales in units]), filter(allselected(sales), [Products] = product && [Month No] = monthno && [Year No] = yearno))
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |