Forum Discussion
Matrix with extra forumulas - how do you do this ??
- 5 years ago
With this model:
Structure table
and these measures:
Sum Amount = SUM(Transactions[Amount])Sum Margin = CALCULATE ( SUM ( Transactions[Amount] ), 'Structure'[Order] IN { 1, 2 }, ALL ( 'Structure' ) )Sum Total Costs = CALCULATE ( SUM ( Transactions[Amount] ), FILTER ( ALL ( 'Structure' ), 'Structure'[Order] > 2 && 'Structure'[Order] < 11 ), ALL ( 'Structure' ) )Sum Operating Result = CALCULATE ( [Sum Total Costs] + [Sum Margin], ALL ( 'Structure' ) )EBITDA = VAR FinInEx = CALCULATE([Sum Amount], 'Structure'[Order] = 13, ALL('Structure')) RETURN [Sum Operating Result] + FinInExand the final measure for the visual:
Amount = SWITCH ( SELECTEDVALUE ( 'Structure'[Order] ), 3, [Sum Margin], 11, [Sum Total Costs], 12, [Sum Operating Result], 14, [EBITDA], [Sum Amount] )You get the following:
I've attached the sample PBIX file
- 5 years ago
Hi Paul, This looks very nice and is exactly what i searching for, thxs a lot !!!!
- 5 years ago
Ok, 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.
Hi Paul, Teh structure is clear, but in what way can i add formulas to this structure? As the excel example.
I can't upload here (no upload function i quess in my account) , but i share this pbix via onedrive:
With this model:
Structure table
and these measures:
Sum Amount = SUM(Transactions[Amount])Sum Margin =
CALCULATE (
SUM ( Transactions[Amount] ),
'Structure'[Order] IN { 1, 2 },
ALL ( 'Structure' )
)
Sum Total Costs =
CALCULATE (
SUM ( Transactions[Amount] ),
FILTER (
ALL ( 'Structure' ),
'Structure'[Order] > 2
&& 'Structure'[Order] < 11
),
ALL ( 'Structure' )
)
Sum Operating Result =
CALCULATE ( [Sum Total Costs] + [Sum Margin], ALL ( 'Structure' ) )
EBITDA =
VAR FinInEx = CALCULATE([Sum Amount], 'Structure'[Order] = 13, ALL('Structure'))
RETURN
[Sum Operating Result] + FinInEx
and the final measure for the visual:
Amount =
SWITCH (
SELECTEDVALUE ( 'Structure'[Order] ),
3, [Sum Margin],
11, [Sum Total Costs],
12, [Sum Operating Result],
14, [EBITDA],
[Sum Amount]
)
You get the following:
I've attached the sample PBIX file
- Bart5 years agoFrequent Visitor
Hi Paul, This looks very nice and is exactly what i searching for, thxs a lot !!!!
- Bart5 years agoFrequent Visitor
Hi Paul, one question about this solution.
Is it possible to get the comparision with last year too in one matrix ?
- PaulDBrown5 years ago
Community Champion
Sure. Can you post a sample PBIX file with data?