Forum Discussion

quickbi's avatar
quickbi
Helper II
2 years ago

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_18's avatar
    Samarth_18
    Community 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
                )
            )
        )