Filtering Calclulations

[quickbi]

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.

[Samarth_18]

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
            )
        )
    )

[Ritaf1983]

Hi @quickbi 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly