Forum Discussion
Filtering Calclulations
Hi all,
I'm doing a table with calculation, and presented with an excel structure template to order this calculations.
The problem come when I do the total row. For example I do Revenues calculations and there is a total revenues raw.
The problem arises when I create the calculation formula. For example, in the total revenues line, I apply a selected value filter indicating that if it is a totals line, it should use the formula that sums the sales. However, it doesn't work.
Revenues Formula:
PL_Revenues = CALCULATE(
[PL_Amount],
'PL Ledger'[Type]="Revenues")Selected Year Actuals Formula that did not work with totals:
Selected Year Actuals =
VAR Current_Item=
SELECTEDVALUE('Income statement'[Level1])
RETURN
SWITCH(
TRUE(),
Current_Item="Total Revenues",[PL_Revenues],
Current_Item="Total COGS",[PL_COGS],
Current_Item="Total Gross Profit",[PL_Gross Margin],
CALCULATE(
[Actuals],
FILTER(
'PL Ledger',
'PL Ledger'[Income statement.Level1] =Current_Item
)
)
)Selected Year Actuals Formula with 1 that works=
Selected Year Actuals with 1 =
VAR Current_Item=
SELECTEDVALUE('Income statement'[Level1])
RETURN
SWITCH(
TRUE(),
Current_Item="Total Revenues",1,
Current_Item="Total COGS",[PL_COGS],
Current_Item="Total Gross Profit",[PL_Gross Margin],
CALCULATE(
[Actuals],
FILTER(
'PL Ledger',
'PL Ledger'[Income statement.Level1] =Current_Item
)
)
)I have verified that the selected value works by substituting the formula with a "1" to see if the problem lies with the selected value, and it shows correctly. I have also checked the formula separately, and it works. But when I put the formula inside the main formula, it doesn't work.
Thank you very much.
2 Replies
- Samarth_18Community Champion
Hi quickbi ,
To answer more specifically we need more information like sample data. However based on your description you could try the below code:-
Selected Year Actuals = VAR Current_Item = SELECTEDVALUE('Income statement'[Level1]) RETURN SWITCH( TRUE(), Current_Item = "Total Revenues", [PL_Revenues], Current_Item = "Total COGS", [PL_COGS], Current_Item = "Total Gross Profit", [PL_Gross Margin], CALCULATE( [Actuals], FILTER( 'PL Ledger', 'PL Ledger'[Income statement.Level1] = Current_Item ) ) )