Calculated measure is not working when i use slicers

[reltakeasy]

Hello there, 

I am new to power bi. I want to transfer a financial report with a very strict and specific format into power bi. So  I have created a table called 'Template' with 3 columns, 'Description', 'Row', and 'GL code'. The description is what the user wants to see, the row is the row number according to Excel.  Also, I have a table called Data which has these columns: 'Amount', 'Date', and 'GL Code'.

Row	GL Code	Description
1	890	Product 1
2	871	Product 2
...	...	...
31	null	Total Product 1 +2

Data Table:

Date	GL Code	Amount
1/1/2023	871	1.40
1/1/2023	890	2.80
2/1/2023	890	2.80

Some rows have to do some sum based on GL Code. For example, when the row number is 31, they must sum the amount with GL Codes 890 and 871. 

So, I wrote a DAX code like this:

SWITCH(true(), MAX('Template'[Row])=31, CALCULATE(SUM(Data[Amount]), FILTER(all('Template'), 'Template'[GL Code] in {890,871

})),0)

It works when I don't use slicers but they give the total for the whole dates. When I use the slicer with Date, it always returns the value 0. What can cause that?

Any help is appreciated.

Thanks in advance 

[Anonymous · Accepted solution]

Hi @reltakeasy ,

You can update the formula of your measure as below, please find the details in the attachment.

Measure = 
IF (
    SELECTEDVALUE ( 'Template'[Row] ) = 31,
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        FILTER ( 'Data', 'Data'[GL Code] IN { 890, 871 } )
    ),
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        FILTER ( 'Data', 'Data'[GL Code] = SELECTEDVALUE ( 'Template'[GL Code] ) )
    )
)Measure = 
IF (
    SELECTEDVALUE ( 'Template'[Row] ) = 31,
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        FILTER ( 'Data', 'Data'[GL Code] IN { 890, 871 } )
    ),
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        FILTER ( 'Data', 'Data'[GL Code] = SELECTEDVALUE ( 'Template'[GL Code] ) )
    )
)

Best Regards