DAX calculated measure only working in a specific row before any FILTER is applied

[TR_TD]

Hi, I have been unable to figure out the following issue:

• I cannot calculate measures in my total (calculated) rows (i.e Total = Measure 1 + Measure 2) of my Expense statement Layout template because Measure 1 (or Measure 2) is only calculated in it's own row and the Total row stays blanks (See Screenshot#1).
• The measure calculations do not work when I use my RM Expense Layout table (using formula #1 or #2)  
• The measure calculations work if I use the (sub)header column (Group Level 2 (CALC) from my CC Mapping Table (equivalent to a Chart of Accounts) with Formula #1

My question is: How can I make the result in screenshot #2 work with my RM Expense Layout table? Why are the measures only working as inteded with the [GROUP Level 2 (CALC)] column table layout?

Below, shows the relationships between my tables

Formula #1: NFRM = CALCULATE(

    SUM('06. Hyperion Data (GL)'[Amount]),

    '03. CC Mapping'[Group Level 2 (CALC)]="Non-Financial Risk."

)

Formula #2:

    SUM('06. Hyperion Data (GL)'[Amount]),

    '05. RM Expense Layout'[RM Expense Layout]="Non-Financial Risk."

)

Screenshot #1:

• The calculated the NFRM measure only works for the header that matches the text in Group Level 2 (CALC) column (CC Mapping table) or the text from the RM Exp Layout column in the RM Expense Layout table.

Screenshot #2:

• Measure works in all the rows before any filter is applied.
• With formula #2, calculation only happens in Non-Financial Risk row.

[techies]

Hi @TR_TD please share the sample pbix file if possible

[v-karpurapud]

Hi @TR_TD 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thank you @Alex_Sawdo for your helpful response.
 

Regarding your query on creating a subtotal in your RM Expense Layout matrix using DAX logic:
 

While I’m not fully aware of the exact structure of your full dataset, I’ve created a sample .pbix file to demonstrate one possible solution that aligns with your goal of making calculated totals appear correctly using the RM Expense Layout table.

Try the below DAX to compute subtotal values correctly in your matrix visual:

NFRM_Mapped_Amount = 
VAR SelectedLayout = SELECTEDVALUE('RM_Expense_Layout'[RM Expense Layout])
RETURN
SWITCH(
    TRUE(),
    SelectedLayout = "Non-Financial Risk.",
        CALCULATE(
            SUM('Hyperion_Data_GL'[Amount]),
            'CC_Mapping'[Group Level 2 (CALC)] = "Non-Financial Risk."
        ),
    SelectedLayout = "Operations",
        CALCULATE(
            SUM('Hyperion_Data_GL'[Amount]),
            'CC_Mapping'[Group Level 2 (CALC)] = "Operations"
        ),
    SelectedLayout = "Technology",
        CALCULATE(
            SUM('Hyperion_Data_GL'[Amount]),
            'CC_Mapping'[Group Level 2 (CALC)] = "Technology"
        ),
    SelectedLayout = "Total",
        CALCULATE(SUM('Hyperion_Data_GL'[Amount])),
    BLANK()
)

I’ve attached a screenshot and the .pbix file demonstrating this logic. Please review them to see if this approach meets your reporting requirements.

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
 

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank You!

[TR_TD]

Hi @v-karpurapud , thank you very much for your response. Unfortunately, the issue is still there and my Total row is still bank/doesn't pick up the created measures.

Here is my data set/source (altered):

1) 05. RM Expense Layout

RM Expense Section	Layout Order	Highlight	RM Expense Layout
Group 1 - Header 1	1	0	Group 1 - Header 1
Group 1 - Header 2	2	0	Group 1 - Header 2
Group 1 - Header 3	3	0	Group 1 - Header 3
Group 1 - Header 4	4	0	Group 1 - Header 4
Group 1 - Header 5	5	0	Group 1 - Header 5
Group 1 - Non-Financial Risk Management	6	0	Group 1 - Header 6
Group 1 - Header 7	7	0	Group 1 - Header 7
Group 1 Total	8	1	Group 1 Total
Group 2 - Header 1	9	0	Group 2 - Header 1
Group 2 - Header 2	10	0	Group 2 - Header 2
Group 2 - Header 3	11	0	Group 2 - Header 3
Group 2 Total	12	1	Group 2 Total

2) 03. CC Mapping 

CC Full	Group Level 1	Group Level 2 (CALC)	Calculation Type	Operator (+ or -)	Order Group level 1	Order Group level 2
Cost_Centre1	Group 1  - SubHeader1	Group 1 - Header 1	Data - Line item	1	1	1
Cost_Centre2	Group 1  - SubHeader1	Group 1 - Header 1	Data - Line item	1	1	1
Cost_Centre3	Group 1  - SubHeader1	Group 1 - Header 1	Data - Line item	1	1	1
Cost_Centre4	Group 1  - SubHeader1	Group 1 - Header 1	Data - Line item	1	1	1
Cost_Centre5	Group 1  - SubHeader1	Group 1 - Header 1	Data - Line item	1	1	1
Cost_Centre6	Group 1 - Subheader 2	Group 1 - Header 2	Data - Line item	1	3	2
Cost_Centre7	Group 1 - Subheader 2.1	Group 1 - Header 2	Data - Line item	1	3	2
Cost_Centre8	Group 1 - SubHeader 3	Group 1 - Header 3	Data - Line item	1	5	3
Less: Projects (Manual)	Group 1 - SubHeader 3.1	Group 1 - Header 3	Data - Line item (Manual)	-1	6
Cost_Centre9	Group 1 - SubHeader 4	Group 1 - Header 4	Data - Line item	1	8	4
Cost_Centre10	Group 1 - SubHeader 4	Group 1 - Header 4	Data - Line item	1	8	4
Cost_Centre11	Group 1 - SubHeader 4	Group 1 - Header 4	Data - Line item	1	8	4
Cost_Centre12	Group 1 - SubHeader 4.1	Group 1 - Header 4	Data - Line item	1	9	4
Cost_Centre13	Group 1 - SubHeader 4.1	Group 1 - Header 4	Data - Line item	1	9	4
Cost_Centre14	Group 1 - SubHeader 4.1	Group 1 - Header 4	Data - Line item	1	9	4
Cost_Centre15	Group 1 - SubHeader 5	Group 1 - Header 5	Data - Line item	1	11	5
Cost_Centre16	Group 1 - SubHeader 5	Group 1 - Header 5	Data - Line item	1	12	5
Cost_Centre17	Group 1 - SubHeader 5.1	Group 1 - Header 5	Data - Line item	1	12	5
Cost_Centre18	Group 1 - SubHeader 5.1	Group 1 - Header 5	Data - Line item	1	12	5
Cost_Centre19	Group 1 - SubHeader 5.1	Group 1 - Header 5	Data - Line item	1	12	5
Cost_Centre20	Group 1 - SubHeader 5.1	Group 1 - Header 5	Data - Line item	1	12	5
Cost_Centre21	Group 1 - SubHeader 5.2	Group 1 - Header 5	Data - Line item	1	13	5
Cost_Centre22	Group 1 - SubHeader 5.2	Group 1 - Header 5	Data - Line item	1	13	5
Cost_Centre23	Group 1 - SubHeader 5.2	Group 1 - Header 5	Data - Line item	1	13	5
Cost_Centre24	Group 1 - SubHeader 5.2	Group 1 - Header 5	Data - Line item	1	13	5
Cost_Centre25	Group 1 - SubHeader 5.2	Group 1 - Header 5	Data - Line item	1	13	5
Cost_Centre26	Group 1 - SubHeader NFRM	Group 1 - Non-Financial Risk Management	Data - Line item	1	15	6
Cost_Centre27	Group 1 - SubHeader 7	Group 1 - Header 7	Data - Line item	1	17	7
Cost_Centre28	Group 1 - SubHeader 7	Group 1 - Header 7	Data - Line item	1	17	7
Cost_Centre29	Group 1 - SubHeader 7	Group 1 - Header 7	Data - Line item	1	17	7
Cost_Centre30	Group 2 - SubHeader 1	Group 2 - Header 1	Data - Line item	1	20	9
Cost_Centre31	Group 2 - SubHeader 1	Group 2 - Header 1	Data - Line item	1	20	9
Cost_Centre32	Group 2 - SubHeader 1	Group 2 - Header 1	Data - Line item	1	20	9
US Amortization (Manual)	Group 2 - SubHeader 2	Group 2 - Header 2	Data - Line item (Manual)	-1	22	10
Projects (Manual)	Group 2 - SubHeader 2.1	Group 2 - Header 2	Data - Line item (Manual)	-1	23	10
Cost_Centre33	Group 2 - subheader 3	Group 2 - Header 3	Calculation (SUM)	-1	25	11
IFRS 9 Amortization (Manual)	Group 2 - subheader 3.1	Group 2 - Header 3	Data - Line item (Manual)	-1	26	11
Cost_Centre34	Group 2 - subheader 3.2	Group 2 - Header 3	Data - Line item	1	27	11

3) 06. Hyperion Data (GL) - don't think you need this but here is a fictious sample

CC Full	Expense Line	Fiscal Year	Scenario	Month	Amount
Cost_Centre1	Total Operating Expenses	2024	Actual	Nov	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Dec	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Jan	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Feb	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Mar	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Apr	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	May	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Jun	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Jul	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Aug	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Sep	10,000.00
Cost_Centre1	Total Operating Expenses	2024	Actual	Oct	10,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Nov	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Dec	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Jan	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Feb	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Mar	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Apr	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	May	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Jun	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Jul	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Aug	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Sep	2,000.00
Cost_Centre1	Total Operating Expenses	2025	QTR2_Forecast	Oct	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Nov	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Dec	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Jan	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Feb	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Mar	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Actual	Apr	3,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Nov	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Dec	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Jan	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Feb	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Mar	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Apr	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	May	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Jun	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Jul	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Aug	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Sep	2,000.00
Cost_Centre1	Total Operating Expenses	2025	Plan	Oct	2,000.00

[v-karpurapud]

Hi @TR_TD 

If possible, please share the PBIX file so that we can better understand how the model contains relationships. We will work on it and be able to provide better assistance to resolve the issue.

Thank You.

[v-karpurapud]

Hi @TR_TD 

It's been a while since we last heard from you. We are ready to assist you with resolving the issue, but we need the necessary details from you. Kindly share the information required so we can better understand and address your issue.

Thank You!

[TR_TD · Accepted solution]

Hi, I was able to get some help from a colleague and the following solution worked to remove filtering from the CC Mapping table which was causing the issue when I used the RM Expense Layout column:

VAR _NFRM =

    CALCULATE(

        [Amounts],

        '05. RM Expense Layout'[RM Expense Layout] = "Non-Financial Risk.",

        REMOVEFILTERS( '03. CC Mapping' )

    )

VAR _Result =

SWITCH(

    SELECTEDVALUE('05. RM Expense Layout'[RM Expense Layout]),

    "FCRM projects.", _FCRM,

                [Amounts]

                )

RETURN

    _Result

[Alex_Sawdo]

A first recommendation could be to re-write the DAX measures to use the FILTER kewyord instead: 
Measure 1: 

CALCULATE(
    SUM(
        '06. Hyperion Data (GL)'[Amount]
    ),
    FILTER(
        VALUES(
            '03. CC Mapping'[Group Level 2 (CALC)]
        )
        '03. CC Mapping'[Group Level 2 (CALC)]="Non-Financial Risk."
    )
)

And the second would be this:

CALCULATE(
    SUM('06. Hyperion Data (GL)'[Amount]),
    FILTER(
        VALUES(
            '05. RM Expense Layout'[RM Expense Layout]
        ),
        '05. RM Expense Layout'[RM Expense Layout] ="Non-Financial Risk."
    )
)

What I'm guessing is happening here is the filter context isn't properly being defined for the DAX measures, so the actual measure can't determine what needs to be filtered. 

[Alex_Sawdo]

My apoligies, I also didn't note the relationships in the diagram. Try changing the relationship between 05. RM Expense Layout and 03. CC Mapping to a Bi-Directional relationship. As it stands now in your model, any filter applied to 03. CC Mapping (whether it be through DAX or visual filters) has no bearing on 05. RM Expense Layout.

[TR_TD]

Thanks for the suggestion. Unfortunately it's still not working,  the measure calculation is still only showing in the NFRM row of the RM Expense Layout with either formula/measure.

[TR_TD]

Here is my screenshot #2 - couldn't include it in the original post