Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to make my expression dynamic by passing variable thru Slicer ?

Hi All

 
Below 2 expression working fine  , But they are not dynamic they are static , it there a way to make the expression i can pass variable from Slicer ? So that i only need one table instead of many table :-
 
_Growth BRAND =
VAR competitor = SELECTEDVALUE ( 'SALES'[BRAND_C])
VAR currentYear = SELECTEDVALUE ( 'Date'[Year] )
VAR currentAMT = SUM ( 'SALES'[sales])
VAR previousAMT = SUMX ( FILTER ( ALLSELECTED( 'SALES' ), 'SALES'[BRAND_C] = competitor && RELATED ( 'Date'[Year] ) = currentYear - 1 ), 'SALES'[sales])
RETURN
DIVIDE ( currentAMT - previousAMT, previousAMT )
 
 
_Growth GROUP =
VAR competitor = SELECTEDVALUE ( 'SALES'[GROUP_CLASS])
VAR currentYear = SELECTEDVALUE ( 'Date'[Year] )
VAR currentAMT = SUM ( 'SALES'[sales])
VAR previousAMT = SUMX ( FILTER ( ALLSELECTED( 'SALES' ), 'SALES'[GROUP_CLASS] = competitor && RELATED ( 'Date'[Year] ) = currentYear - 1 ), 'SALES'[sales])
RETURN
DIVIDE ( currentAMT - previousAMT, previousAMT )
 
Above link is my PBI file
 
Paul Yeo
 

5 Replies

  • Anonymous , if you slicer from the table or table joined to it you do need to use selected value. Also what do you  mean by not dynamic ?

    use in 'SALES'[GROUP_CLASS] in  values( 'SALES'[GROUP_CLASS]) for all values. Selectedvalue will give only one value.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Amit

      Thank you for your fast reply. 

      Dynamic = Only using one Table and user can select to view report Brand or SBU by click the Slicer. 

      Paul