Forum Discussion
P&L based on a Table
- Anonymous1 year ago
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.
Hola Msampedro
VM % =
DIVIDE(
SUM('Table'[Variable Contribution]),
SUM('Table'[Net Sales])
)
GM % =
DIVIDE(
SUM('Table'[Gross Profit]),
SUM('Table'[Net Sales])
)
Luego, en tu matriz:
Coloca Attribute en las Filas
Coloca las nuevas medidas (VM %, GM %, etc.) en los Valores
Agrega Año o Mes en las Columnas o como filtro, según sea necesario