Forum Discussion
Helper ITable 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 | 3437 |
C | 2023 | 5998 |
I have data for many different teams and many different years.
The output should be a table like this:
| Team | 2023 | 2022 | Delta |
| A | 8788 | 7000 | 1788 |
| B | 3437 | 4634 | -1197 |
| C | 5998 | 7122 | -1124 |
I have a drop down in the dashboard where I can select the two years to compare, but the output will still show the results for all teams.
What's the best way to achieve this kind of table?
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.
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!
5 Replies
- Ritaf1983
Super User
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.
zervinoThe 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
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.
ajohnso2Solution Supplier
1. Add a dimension table of your years
2. Add a dimension table of your teams
3. Join years & team table to your 'Fact'
4. Use dax to calculate a new measure for LY