Forum Discussion

Sekhar1's avatar
Sekhar1
Frequent Visitor
3 years ago
Solved

Dynamic Columns based on Slicer selection

Hello,

 

I have below sample data with 4 Columns. 

 

CountryAccountYearValue
AUSEBITDA202213
AUSEBITDA202310
AUSEBITDA201898
AUSEBITDA2021120
AUSEBITDA2020700
AUSEBITDA201910
AUSSales202013
AUSSales202110
AUSSales202339
AUSSales2019120
AUSSales2018145
AUSSales202228
AUSITDA202310
AUSITDA2022231
AUSITDA202139

 

When User selects 2020 from Slicer, Clustered Chart should display below Columns only :-

  1. EBITDA for 2020 and last 2 years (2020, 2019 and 2018).
  2. Sales for 2020 only
  3. ITDA for next 2 years (i.e. 2021 and 2022)

Chart shall be displayed as per below:-

 

 

 

Can you please advise on how to get the above result?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Sekhar1 ,

     

    Here I suggest you to try this code to create a measure.

    Measure = 
VAR _SELECTYEAR = SELECTEDVALUE('Table 2'[Year])
VAR _VALUE = 
IF(
    ISFILTERED('Table 2'[Year]),
    SWITCH(
        MAX('Table'[Account]),
        "EBITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>=_SELECTYEAR-2&&'Table'[Year]<=_SELECTYEAR)),
        "Sales",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=_SELECTYEAR)),
        "ITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>_SELECTYEAR&&'Table'[Year]<=_SELECTYEAR+2))
        )
    )
RETURN
_VALUE

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Sekhar1 ,

     

    Here I suggest you to try this code to create a measure.

    Measure = 
VAR _SELECTYEAR = SELECTEDVALUE('Table 2'[Year])
VAR _VALUE = 
IF(
    ISFILTERED('Table 2'[Year]),
    SWITCH(
        MAX('Table'[Account]),
        "EBITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>=_SELECTYEAR-2&&'Table'[Year]<=_SELECTYEAR)),
        "Sales",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=_SELECTYEAR)),
        "ITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>_SELECTYEAR&&'Table'[Year]<=_SELECTYEAR+2))
        )
    )
RETURN
_VALUE

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.