Forum Discussion
Solved
Filtered measure
Hello, I have the following table. What would be the correct measure to use so that if a year and salesperson is selected, the specific salesperson's sales (for the selected year) will be calcula...
hello Oros
please check if this accomodate your need.
create a new measure with following DAX.
Measure =
var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
var _Year = SELECTEDVALUE('Table'[Year])
Return
DIVIDE(
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
)Hope this will help.
Thank you.
Hi,
PBI file attached.
Hope this helps.
hello Oros
please check if this accomodate your need.
create a new measure with following DAX.
Measure =
var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
var _Year = SELECTEDVALUE('Table'[Year])
Return
DIVIDE(
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
)
Hope this will help.
Thank you.
Hello Irwan,
Thank you for your quick reply. What if I have a separate calendar table. What should be the exact measure to use? Thanks again.
hello Oros
since this is measure, you can change a little bit in _Year.
Measure =
var _Salesperson = SELECTEDVALUE('Table'[SALESPERSON])
var _Year = SELECTEDVALUE('Table (2)'[Year])
Return
DIVIDE(
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[SALESPERSON]=_Salesperson&&'Table'[Year]=_Year)),
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[SALESPERSON]=_Salesperson))
)Here is the result, show the same result even though it takes value from different table ('Table (2)').the slicer is also taking value from the Date table.
Hope this will help.
Thank you.