Help with ignoring row context

[DaveAng]

Hi!

I have two tables; One dimension with accounting codes and one fact table with transactions. 

I did an easy sum on the transactions like this: 

Total = SUM(Fact_table[AccountingAmount])

But I want to show all accounts, even if there are no transactions. This is the measure that I created:

Total_all_accounts =

IF (

    [Total] = BLANK (),

    0 + 'Measures Table'[Total],

    [Total]

)

This is the result, and also the result i wanted. So far so good:

But it is now when things are starting to get tricky for me. I want the sum of a specific range of accounts to be summarized in account no 1099. First I did a measure to define the accounts that should be included (1010-1098). Since the data type of the Account is text I used FORMAT:

1099 =

CALCULATE(

                [Total_all_accounts],

                FILTER(DimAccounting,

                DimAccounting[Account] >= FORMAT(1010, "Fixed")

                && DimAccounting[Account] <= FORMAT(1098, "Fixed")

            ))

When I use the measure in a card visualization I get the result I want. Then I tried to put it all together. I want the measure to display [Total_all_accounts] on every row except for the account 1099, where the measure [1099] should be displayed. Here is the measure I created :

Total_test =

VAR Account =

   SELECTEDVALUE (DimAccounting[Account])

RETURN

    SWITCH (

        Account,

        "1099",

        [1099]  ,

        [Total_all_accounts]

        )

And here is the result:

[Total_test] give me the same result as [Total_all_accounts], but I want the sum of accounts 1010-1098 on the 1099 account. My best guess is that's because of row context. I hope someone can help me with my issue and show me how to ignore row context on 1099 (or if I made some other misstakes in the previous steps). 

Thanks!

Best regards

[v-cgao-msft · Accepted solution]

Hi @DaveAng ,

Please try.

Total_all_accounts = [Total] + 0

1099 =
SUMX (
    FILTER (
        ALL ( 'Dim_Table' ),
        'Dim_Table'[Account] >= "1010"
            && 'Dim_Table'[Account] <= "1098"
    ),
    [Total_all_accounts]
)

Total_test = 
SWITCH(
    SELECTEDVALUE('Dim_Table'[Account]),
    "1099",
    [1099],
    [Total_all_accounts]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DaveAng]

Hi,

Thank you! Your solution worked just fine!

BR