cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need help with representing percentage differences in matrix table

 Date of Action Customer Volume Transaction Assets 13/01/2020 Customer A 44,499.2 1,148 60 5/06/2021 Customer A 145,243.4 508 106 26/06/2022 Customer A 758.7 15 2 28/11/2023 Customer A 1,669.1 29 8 25/07/2024 Customer A 15,330.9 287 51 5/03/2020 Customer B 2,186.1 16 1 26/08/2021 Customer B 631.6 16 4 15/08/2023 Customer B 220,562.9 854 63 23/05/2024 Customer B 26,053.9 103 29 5/04/2022 Customer C 1,423.1 32 12 31/03/2023 Customer C 62,440.1 214 58 4/08/2023 Customer C 2,245.7 17 1 6/03/2024 Customer C 42,202.8 58 12

This is a sample of my dataset that im putting into excel. Below is what the matrix table looks like on Power Bi

Is there a way to input columns next to "sum of assets", another next to "sum of transaction" and "sum of volume" that shows the decrease or increase as a percentage over the years? Ideally when we drill down and expand on the months - it will also show the percentage differences between the months too. Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @derekli1700 ,

Maybe you need create a Date table. And then try formula like below:

``````Assets_Pct_Change =
VAR CurrentYearAssets =
SUM ( 'FactTable'[Assets] )
VAR PreviousYearAssets =
CALCULATE ( SUM ( 'FactTable'[Assets] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
DIVIDE ( CurrentYearAssets - PreviousYearAssets, PreviousYearAssets, 0 )
``````
``````Transaction_Pct_Change =
VAR CurrentYearTransaction =
SUM ( 'FactTable'[Transaction] )
VAR PreviousYearTransaction =
CALCULATE (
SUM ( 'FactTable'[Transaction] ),
DATEADD ( 'Date'[Date], -1, YEAR )
)
RETURN
DIVIDE (
CurrentYearTransaction - PreviousYearTransaction,
PreviousYearTransaction,
0
)
``````
``````Volume_Pct_Change =
VAR CurrentYearVolume =
SUM ( 'FactTable'[Volume] )
VAR PreviousYearVolume =
CALCULATE ( SUM ( 'FactTable'[Volume] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
DIVIDE ( CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, 0 )
``````

Best Regards,

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

3 REPLIES 3
Community Support

Hi @derekli1700 ,

Maybe you need create a Date table. And then try formula like below:

``````Assets_Pct_Change =
VAR CurrentYearAssets =
SUM ( 'FactTable'[Assets] )
VAR PreviousYearAssets =
CALCULATE ( SUM ( 'FactTable'[Assets] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
DIVIDE ( CurrentYearAssets - PreviousYearAssets, PreviousYearAssets, 0 )
``````
``````Transaction_Pct_Change =
VAR CurrentYearTransaction =
SUM ( 'FactTable'[Transaction] )
VAR PreviousYearTransaction =
CALCULATE (
SUM ( 'FactTable'[Transaction] ),
DATEADD ( 'Date'[Date], -1, YEAR )
)
RETURN
DIVIDE (
CurrentYearTransaction - PreviousYearTransaction,
PreviousYearTransaction,
0
)
``````
``````Volume_Pct_Change =
VAR CurrentYearVolume =
SUM ( 'FactTable'[Volume] )
VAR PreviousYearVolume =
CALCULATE ( SUM ( 'FactTable'[Volume] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
DIVIDE ( CurrentYearVolume - PreviousYearVolume, PreviousYearVolume, 0 )
``````

Best Regards,

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

Frequent Visitor

@v-kongfanf-msft hi - the calculations doesnt seem to work when you drill down into month? It works for YOY difference but no month by month difference? Thanks

Super User

maybe you can try DATEADD function to get next month's value

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors