Forum Discussion
Helper IP&L based on a Table
Hi Msampedro ,
Thank you for more provideing more details.I would be happy to assist you!
Please include below measures :PnL Value =
VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])
VAR NetSales =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Net Sales"
)
VAR VarContribution =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Variable Contribution"
)
VAR GrossProfit =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Gross Profit"
)
RETURN
SWITCH(
TRUE(),
SelectedAttribute = "VM %", DIVIDE(VarContribution, NetSales),
SelectedAttribute = "GM %", DIVIDE(GrossProfit, NetSales),
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = SelectedAttribute
)
)PnL Value (Formatted) =
VAR BaseValue = [PnL Value]
VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])
RETURN
SWITCH(
TRUE(),
SelectedAttribute IN {"VM %", "GM %"}, FORMAT(BaseValue, "0.00%"),
FORMAT(BaseValue, "#,##0")
)
Then add a matrix viual.For rows,add PnL Structure[Attribute], for Column add Month or Year upon requirement and for values add PnL Values(formatted) measure.
Please refer the screenshot and file for your reference.
If this answer meets your requirements,consider accepting it as solution.If still facing the issues,feel free to reachout!
Thank you.
Helper IHi burakkaragoz Anonymous Khushidesai0109 ,
Sharing an example of my current table, after unpivoted needed attributes (Net Sales, Gross Profit...). From this I need to calculate VM% and GM% to include them in a Matrix, as a single row (see what I am looking for on my second capture). Many thanks in advance.
Hi Msampedro ,
Thank you for more provideing more details.I would be happy to assist you!
Please include below measures :
PnL Value =
VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])
VAR NetSales =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Net Sales"
)
VAR VarContribution =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Variable Contribution"
)
VAR GrossProfit =
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = "Gross Profit"
)
RETURN
SWITCH(
TRUE(),
SelectedAttribute = "VM %", DIVIDE(VarContribution, NetSales),
SelectedAttribute = "GM %", DIVIDE(GrossProfit, NetSales),
CALCULATE(
SUM('Unpivoted Table'[Value]),
'Unpivoted Table'[Attribute] = SelectedAttribute
)
)
PnL Value (Formatted) =
VAR BaseValue = [PnL Value]
VAR SelectedAttribute = SELECTEDVALUE('PnL Structure'[Attribute])
RETURN
SWITCH(
TRUE(),
SelectedAttribute IN {"VM %", "GM %"}, FORMAT(BaseValue, "0.00%"),
FORMAT(BaseValue, "#,##0")
)
Then add a matrix viual.For rows,add PnL Structure[Attribute], for Column add Month or Year upon requirement and for values add PnL Values(formatted) measure.
Please refer the screenshot and file for your reference.
If this answer meets your requirements,consider accepting it as solution.If still facing the issues,feel free to reachout!
Thank you.
- Msampedro
This is working, many thanks!! Already accepted as a solution.
One additional question, I am adding one more Attribute/calculation - Do you know How I can give decimal formar? I have tried this one but not working (syntax error)
VAR QtyLiter =CALCULATE(SUM('P&L'[Value]),'P&L'[Attribute] = "Qty (Liter)")RETURNSWITCH(TRUE(),SelectedAttribute = "VM %", DIVIDE(VarContribution, NetSales),SelectedAttribute = "GM %", DIVIDE(GrossProfit, NetSales),SelectedAttribute = "ASP, EUR/lt", DIVIDE(NetSales, QtyLiter),CALCULATE(SUM('P&L'[Value]),'P&L'[Attribute] = SelectedAttribute))Hi Msampedro ,
Thank you for the folow-up question!
The logic looks good, but the issue might due to SelectedAttribute being used without being defined inside your measure. You may need to declare it as a variable first.
II hope this helps! If you are still experiencing the same issue, please feel free to raise a new thread — we will be happy to assist you further.
Thank you for your understanding!