Forum Discussion

zervino's avatar
zervino
Icon for Helper I rankHelper I
2 years ago
Solved

Table with year and deltas

I have raw data with this format:   Team Year Sales A 2021 6345 B 2021 4554 C 2021 8525 A 2022 7000 B 2022 4634 C 2022 7122 A 2023 8788 B 2023 343...
Show and Tell
Tips and Tricks
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi zervino 

    You can apply these steps :
    1. Create the table for Years :

    Years = DISTINCT('Table'[Year])

    2. create a relationship with your table:

    3. Create 3 measures :

    Max Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=max('Years'[Year]) )
    Min Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=min('Years'[Year]) )
    Delta = [Max Year]-[Min Year]
    4. create a visual:

    pbix is attached

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi zervino 

     

    You can create a measure like below. Use a matrix visual instead of a table visual. Add Team to rows, Add Year to column and add the measure to Values. Rename the column total from the default "Total" to "Delta". 

    Value = IF(ISINSCOPE(Years[Year]),SUM('Table'[Sales]),[Delta])

     

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

     

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
2 years ago

Hi zervino 

You can apply these steps :
1. Create the table for Years :

Years = DISTINCT('Table'[Year])

2. create a relationship with your table:

3. Create 3 measures :

Max Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=max('Years'[Year]) )
Min Year = CALCULATE(sum('Table'[Sales]) ,'Years'[Year]=min('Years'[Year]) )
Delta = [Max Year]-[Min Year]
4. create a visual:

pbix is attached

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

zervino's avatar
zervino
Icon for Helper I rankHelper I
2 years ago

The problem with this approach is that the column title is not dynamic, it would be "max year", but depending on which years the user chooses, I would like to see eg. 2022 and 2016

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    2 years ago

    zervino 

    For dynamic measure names you can use a workaround with field parameters .
    Please refer to the linked video:

    https://www.youtube.com/watch?v=9_2m5Csr55c

     

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi zervino 

     

    You can create a measure like below. Use a matrix visual instead of a table visual. Add Team to rows, Add Year to column and add the measure to Values. Rename the column total from the default "Total" to "Delta". 

    Value = IF(ISINSCOPE(Years[Year]),SUM('Table'[Sales]),[Delta])

     

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!