Forum Discussion
Solved
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 ...
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:
PaulDBrown
Community Champion
Community ChampionWith 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
Hi Paul, This looks very nice and is exactly what i searching for, thxs a lot !!!!
Hi Paul, one question about this solution.
Is it possible to get the comparision with last year too in one matrix ?
- PaulDBrown
Sure. Can you post a sample PBIX file with data?