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.
Hi I have an issue with Matrix subtotal. So i have one measure where i calculate each line's value and a 2nd measure where i used a crossfilter to put a value to all lines. Then i created a third measure to merge both first and second measures in one column. See below:
Measure 1: BS02AsAtAllTransactionAmountDKK = CALCULATE(sum(GLTransClient[Amount]),DATESBETWEEN('Calendar'[Date],[BSFirstDate],[BSDateSelected]))
Measure 2: BS03NetProfit = CALCULATE(CALCULATE(sum(GLTransClient[Amount]),all(BSGLCoAHeaders[Count.Level0]),all('Calendar'[Date]),DATESBETWEEN('Calendar'[Date],[BSFirstDate],[BSDateSelected]),value(GLTransClient[Account])<5000000),CROSSFILTER(GLAccountClient[Account],GLChartOfAccounts[Account],None))
Measure 3: BS04AsAtAllTransactionAmountDKKwithResultsYTD =
Up to now, all is working fine but when i collapse the CAPITAL AND RESERVES, the Results year to date is not being added to the subtotal.
All the other subtotals in the same column are working fine.
Grateful for any kind assistance.
Solved! Go to Solution.
Hi @Ackbar-Learner
Please try
BS04AsAtAllTransactionAmountDKKwithResultsYTD =
SUMX (
SUMMARIZE ( GLChartOfAccounts, GLChartOfAccounts[L1], GLChartOfAccounts[L2] ),
CALCULATE (
VAR result = [BS02AsAtAllTransactionAmountDKK]
RETURN
IF (
SELECTEDVALUE ( GLChartOfAccounts[L2] ) = "Results year to date",
VALUE ( [BS03NetProfit] ),
result
)
)
)
Hi @Ackbar-Learner
Please try
BS04AsAtAllTransactionAmountDKKwithResultsYTD =
SUMX (
SUMMARIZE ( GLChartOfAccounts, GLChartOfAccounts[L1], GLChartOfAccounts[L2] ),
CALCULATE (
VAR result = [BS02AsAtAllTransactionAmountDKK]
RETURN
IF (
SELECTEDVALUE ( GLChartOfAccounts[L2] ) = "Results year to date",
VALUE ( [BS03NetProfit] ),
result
)
)
)
Perfect Mate! With this, I completed the Balance Sheet 😄 Thanks a lot.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |