Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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))
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |