Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

create a new % Change column to compare values based on multiple slicers including on the same field

Hi there,

 

I am trying to figure out how to have a table visual with a column which updates based on multiple slicer selections.

 

So far, I've got two table visuals showing the Boroughs of London and their respective populations for different years (as selected in two separate slicers). In the report view,  I have linked each of these table visuals to a slicer where the year for each can be selected and seen in the tables. 

 

In the 3rd table visual I would like to have a new column showing the % change between the two years selected by the user to show the % growth projected (i.e. (2037 Population- 2022 Population)/ 2022 Population=x%).

 

My understanding is that I cannot reference those other table visuals and that I have to use a measure. Just having a tough time figuring out how best to go about this. Any help would be greatly appreciated. 

 

Kind regards, A

 

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    Do you have two watches? A fact table and a prediction table or a prediction and a fact table in the same table?
    Assume that they are in the same table. You need create a new table for the second year slicer. Then use the following measure to do calculate %:

    Measurde =
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[values] ),
        FILTER ( ALL ( 'Table'[year] ), [year] = SELECTEDVALUE ( 'Year'[year] ) )
    )
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( 'Year'[year] ) ),
        "please select one",
        FORMAT (
            DIVIDE ( _1 - SUM ( 'Table'[values] ), SUM ( 'Table'[values] ) ),
            "#.0%"
        )
    )

     

    Result:

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Do you have two watches? A fact table and a prediction table or a prediction and a fact table in the same table?
    Assume that they are in the same table. You need create a new table for the second year slicer. Then use the following measure to do calculate %:

    Measurde =
VAR _1 =
    CALCULATE (
        SUM ( 'Table'[values] ),
        FILTER ( ALL ( 'Table'[year] ), [year] = SELECTEDVALUE ( 'Year'[year] ) )
    )
RETURN
    IF (
        ISBLANK ( SELECTEDVALUE ( 'Year'[year] ) ),
        "please select one",
        FORMAT (
            DIVIDE ( _1 - SUM ( 'Table'[values] ), SUM ( 'Table'[values] ) ),
            "#.0%"
        )
    )

     

    Result:

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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