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

[Paulyeo11]

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 )

https://www.dropbox.com/s/78masspm617220k/PBT_SAMPLE%20100%20ROW%20JAN%202021%20V005.pbix?dl=0

Above link is my PBI file

Paul Yeo

[amitchandak]

@Paulyeo11 , 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.

[Paulyeo11]

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

[amitchandak]

@Paulyeo11 , Not clear. Can you explain

[Paulyeo11]

Hi Amit

Below link is my PBI file :-

https://www.dropbox.com/s/78masspm617220k/PBT_SAMPLE%20100%20ROW%20JAN%202021%20V005.pbix?dl=0

Now i need to have 2 Table , i want to reduce to 1 Table . 

I am thinking using Slicer , since BRNAD_C and GROUP_CLASS they are fields , i cannot put in Slicer. not sure how to use personlise mode , allow user change field.

[amitchandak · Accepted solution]

@Paulyeo11 , Based on what I got.

One option - Bookmark and buttons. - https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

refer to these two

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/