Forum Discussion

BIAB's avatar
BIAB
Icon for Resolver I rankResolver I
7 years ago
Solved

HASONEFILTER behavior on Matix with multiple row levels.

I'm using this small formula :-) to calculate the Balance Sheet balance (running total).  The Matrix has 5 Rows (Type, Category, Sub Category, Account, URL/Transaction) and 1 Column (Year/Month).  So If I expand the matrix all the way to the URL I can see the correct amount for each URL transactions and the SubTotal of the Account and SubCategory is fine but when I add all the SubCategories the amount of the Category has a variance. 

I'm not expecting anyone to solve this for me but at least to give me some guidance.  I believe that because I'm only checking if a filter is applied to HASONEFILTER ( AccountLedgerUnion[URL] )
that I may need to do something to calculate the other row levels (up level) correctly.  I have search the internet and has not found anyone talkinga bout this so maybe I'm wrong.  Any input is appreciated.

 

Need Help
  • BIAB's avatar
    BIAB
    7 years ago

    Again I found the answer to my own problem ;-).  Not surprisingly the issue was not what I imagined, that explains why I coudnt find any document online on implementing hasonefilter on a multi row level matrix. 

    Even if I don't get an answer here it was helpull just to express it and thing more about it.   The issue was in my formula.  I'm highliting below where the issue was.

2 Replies

  • BIAB's avatar
    BIAB
    Icon for Resolver I rankResolver I
    7 years ago
    Running Total =
IF (
    MINX ( 'Date', 'Date'[Date] )
        <= CALCULATE (
            MAXX ( TRANSACTIONS, TRANSACTIONS[TRANDATE] ),
            ALL ( TRANSACTIONS )
        ),
    IF (
        HASONEFILTER ( AccountLedgerUnion[URL] ),
        --ROW TOTAL 
        SWITCH (
            CALCULATE (
                MAXX ( AccountLedgerUnion, VALUE ( AccountLedgerUnion[Subsidiary Parent ID] ) ),
                ALL ( TRANSACTIONS )
            ),
            3, CALCULATE (
                SUMX ( AccountLedgerUnion, AccountLedgerUnion[Amount2] ),
                FILTER (
                    ALLSELECTED ( 'Date'[Date] ),
                    'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                )
            ) * [Last Current Consolidated FX EUR] * [Last Current Consolidated FX],
            1, CALCULATE (
                SUMX ( AccountLedgerUnion, AccountLedgerUnion[Amount2] ),
                FILTER (
                    ALLSELECTED ( 'Date'[Date] ),
                    'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                )
            ) * [Last Current Consolidated FX],
            999, CALCULATE (
                SUMX ( AccountLedgerUnion, AccountLedgerUnion[Amount2] ),
                FILTER (
                    ALLSELECTED ( 'Date'[Date] ),
                    'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                )
            ),
            990, 0
        ),
        --SUBTOTAL 
        IF (
            MINX ( 'Date', 'Date'[Date] )
                <= CALCULATE (
                    MAXX ( AccountLedgerUnion, AccountLedgerUnion[Transaction Date] ),
                    ALL ( AccountLedgerUnion )
                ),
            VAR __period =
                CALCULATE (
                    MAX ( ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID] ),
                    USERELATIONSHIP ( ACCOUNTING_PERIODS[YearMonthNo], 'Date'[YearMonthNo] )
                )
            RETURN
                VAR __totalParentID3 =
                    CALCULATE (
                        SUMX (
                            FILTER ( AccountLedgerUnion, AccountLedgerUnion[Subsidiary Parent ID] = "3" ),
                            AccountLedgerUnion[Amount2]
                                * LOOKUPVALUE (
                                    ConsolidatedExchangeRatesEUROPE[CURRENT_RATE],
                                    ConsolidatedExchangeRatesEUROPE[Key], "3^1-" & MAX ( AccountLedgerUnion[Accounting Period] )
                                )
                                * LOOKUPVALUE (
                                    CONSOLIDATED_EXCHANGE_RATES[CURRENT_RATE],
                                    CONSOLIDATED_EXCHANGE_RATES[Key], AccountLedgerUnion[Subsidiary ID] & "^" & AccountLedgerUnion[Subsidiary Parent ID] & "-" & __period
                                )
                        ),
                        FILTER (
                            ALLSELECTED ( 'Date'[Date] ),
                            'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                        )
                    )
                VAR __totalParentID1 =
                    CALCULATE (
                        SUMX (
                            FILTER ( AccountLedgerUnion, AccountLedgerUnion[Subsidiary Parent ID] = "1" ),
                            AccountLedgerUnion[Amount2]
                                * LOOKUPVALUE (
                                    CONSOLIDATED_EXCHANGE_RATES[CURRENT_RATE],
                                    CONSOLIDATED_EXCHANGE_RATES[Key], AccountLedgerUnion[Subsidiary ID] & "^" & AccountLedgerUnion[Subsidiary Parent ID] & "-" & __period
                                )
                        ),
                        FILTER (
                            ALLSELECTED ( 'Date'[Date] ),
                            'Date'[Date] <= MAXX ( 'Date', 'Date'[Date] )
                        )
                    )
                VAR __totalParentID999 =
                    CALCULATE (
                        TOTALYTD (
                            SUMX (
                                FILTER ( AccountLedgerUnion, AccountLedgerUnion[Subsidiary Parent ID] = "999" ),
                                AccountLedgerUnion[Amount2]
                            ),
                            'Date'[Date]
                        )
                    )
                VAR __totalParentID990 =
                    CALCULATE (
                        SUMX (
                            FILTER ( AccountLedgerUnion, AccountLedgerUnion[Subsidiary Parent ID] = "990" ),
                            AccountLedgerUnion[Amount2]
                        ),
                        FILTER (
                            ALLSELECTED ( 'Date'[Date] ),
                            'Date'[Date]
                                <= DATEVALUE ( "12/31/" & YEAR ( MAX ( 'Date'[Date] ) ) - 1 )
                        )
                    )
                RETURN
                    __totalParentID1 + __totalParentID3 + __totalParentID990 + __totalParentID999
        )
    )
)
    • BIAB's avatar
      BIAB
      Icon for Resolver I rankResolver I
      7 years ago

      Again I found the answer to my own problem ;-).  Not surprisingly the issue was not what I imagined, that explains why I coudnt find any document online on implementing hasonefilter on a multi row level matrix. 

      Even if I don't get an answer here it was helpull just to express it and thing more about it.   The issue was in my formula.  I'm highliting below where the issue was.