Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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 ) ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |