Forum Discussion
Bart
5 years agoFrequent Visitor
Matrix with extra forumulas - how do you do this ??
Hi All, I hope you have a solution for this. I have a financial transaction table and want to make a matrix like the excel example below: Excel example But what is the way to add some ...
PaulDBrown
Community Champion
5 years agoOk, so you have a couple of options which are basically aesthetically driven choices. You can either have the following:
Using...
diff € vs. Last Year =
IF (
SELECTEDVALUE ( 'Date'[Year] ) = YEAR ( TODAY () ),
[Amount] - CALCULATE ( [Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )
)
% diff vs Prev year =
DIVIDE (
[diff € vs. Last Year],
CALCULATE ( [Amount], DATEADD ( 'Date'[Date], -1, YEAR ) )
)
But this invloves hacking the matrix visual to hide the irrelevant columns and turning off "word wrap" in the column formatting options and dragging the column boundaries...
or you can create this:
using...
This Year = CALCULATE([Amount], 'Date'[Year] = YEAR(TODAY()))Last Year = CALCULATE([Amount], 'Date'[Year] = YEAR(TODAY())-1)Diff vs last year = [This Year] - [Last Year]% diff vs last year = DIVIDE([Diff vs last year], [Last Year])
But as you can see the column headings are not dynamic ("This Year" & "Last Year")
So it is really up to you...
I've attached the sample PBIX file.
Bart
5 years agoFrequent Visitor
Hi Paul, nice, thank you for your quick response and solution!