Forum Discussion
Aazam
Helper I
2 years agoRow is not adding in SUM
I am having an issue in total, I have created a templet through dataflow, and I know the issue is it is getting 0 value from that templet that is my native behavior of PowerBI is not calculating this...
Aazam
Helper I
2 years agoIt blanks those rows, instead of excluding them from the sum
[Combine-Stack-Total Actual MTD -- Testing Aazam 2 ] =
SUMX (
FILTER (
SUMMARIZE(
'GL Department Accounts-Stack',
'GL Department Accounts-Stack'[Accounts Group],
'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID],
'GL Department Accounts-Stack'[GL Department]),
Not CONTAINSSTRING('GL Department Accounts-Stack'[GL Department],"%")
),
VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
VAR totalactualMTD = [Total Actual MTD]
VAR Cgphomeofficeincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,
ALL ( 'GL Department Accounts-Stack' )
)
VAR CGPEXPENSES =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,
ALL ( 'GL Department Accounts-Stack' )
)
VAR CgpHomeOffice = ((CGPEXPENSES*-1)+CGPhomeofficeincome)
VAR totalrevenue =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 1,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalcogs =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalpayrollexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 10,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totaloperatingexpenses =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 15,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalexternalexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 21,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalrentexpense =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 27,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalotherexpesnsesincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 32,
ALL ( 'GL Department Accounts-Stack' )
)
VAR mercsalesGolfOperation =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 104,
ALL ( 'GL Department Accounts-Stack' )
)
VAR mercsalesFBCatering =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 105,
ALL ( 'GL Department Accounts-Stack' )
)
VAR cogsGolfOperation =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 10,
ALL ( 'GL Department Accounts-Stack' )
)
VAR cogsFBcateringBanquet =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4
&& 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 12,
ALL ( 'GL Department Accounts-Stack' )
)
VAR totalexternalexpensefinal = ( totalexternalexpense + CgpHomeOffice )
VAR grossprofit = ( totalrevenue - totalcogs )
VAR grossprofitpercentage =
DIVIDE ( grossprofit, totalrevenue, 0 )
VAR revpercentage1 =
DIVIDE ( totalpayrollexpense, totalrevenue, 0 )
VAR clublevelEbitdar = ( grossprofit - totalpayrollexpense - totaloperatingexpenses )
VAR revpercentage2 =
DIVIDE ( clublevelEbitdar, totalrevenue, 0 )
VAR corporateEbitdar = ( clublevelEbitdar - totalexternalexpensefinal )
VAR revpercentage3 =
DIVIDE ( corporateEbitdar, totalrevenue, 0 )
VAR corporateEbitda = ( corporateEbitdar - totalrentexpense )
VAR netincome = ( corporateEbitda - totalotherexpesnsesincome )
VAR cogsgolfoperper =
DIVIDE ( cogsGolfOperation, mercsalesGolfOperation, 0 )
VAR cogsfbbanquertper =
DIVIDE ( cogsFBcateringBanquet, mercsalesFBCatering, 0 )
VAR xxx =
SWITCH (
selectvalue,
48, CgpHomeOffice,
17, grossprofit,
18, grossprofitpercentage,
29, revpercentage1,
43, clublevelEbitdar,
44, revpercentage2,
51, corporateEbitdar,
52, revpercentage3,
57, corporateEbitda,
74, netincome,
11, cogsgolfoperper,
13, cogsfbbanquertper,
totalactualMTD
)
//Horizontal Report Structure
VAR CalcType =
SELECTEDVALUE ( 'GL Department Accounts-Stack'[Calc Type] ) // should it be a simple total or a running total?
VAR DisplayDetailCode =
SELECTEDVALUE ( 'GL Department Accounts-Stack'[Show Detail] ) // 1 if the detail should display and 0 if detail should be hidden
VAR isSubHeaderVisible =
ISFILTERED ( 'GL Department Accounts-Stack'[Stack_Dept_Name] ) //we need to know if the sub header is trying to show itself
VAR ismainHeaderVisible =
ISFILTERED ( 'GL Department Accounts-Stack'[Stack Accounts Group] ) //we need to know if the sub header is trying to show itself
VAR AccountGroupID =
SELECTEDVALUE ( 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] ) // Get the selected Account Group ID
VAR reeturn =
SWITCH (
TRUE (),
isSubHeaderVisible = TRUE ()
&& DisplayDetailCode = "0"
&& CalcType = "20"
&& AccountGroupID <> 37
&& AccountGroupID <> 39
&& AccountGroupID <> 7
&& AccountGroupID <> 8
&& AccountGroupID <> 13
&& AccountGroupID <> 18
&& AccountGroupID <> 19
&& AccountGroupID <> 24
&& AccountGroupID <> 25
&& AccountGroupID <> 30
&& AccountGroupID <> 35, BLANK (),
isSubHeaderVisible = TRUE ()
&& AccountGroupID = 37, REPT ( "A", 10 ),
isSubHeaderVisible = TRUE ()
&& AccountGroupID = 39, REPT ( "A", 10 ),
//[Stack-Total Actual MTD]
xxx
)
RETURN
reeturn
)
tamerj1
Community Champion
2 years agoPlease try
Combine-Stack-Total Actual MTD =
IF (
CONTAINSSTRING (
SELECTEDVALUE ( 'GL Department Accounts-Stack'[GL Department] ),
"%"
),
[Actual MTD],
SUMX (
FILTER (
SUMMARIZE (
'GL Department Accounts-Stack',
'GL Department Accounts-Stack'[Accounts Group],
'GL Department Accounts-Stack'[GL Department]
),
NOT CONTAINSSTRING ( 'GL Department Accounts-Stack'[GL Department], "%" )
),
[Actual MTD]
)
)
- Aazam2 years ago
Helper I
Will get an error because of the ending parenthesis.
[Combine-Stack-Total Actual MTD -- Testing Aazam 2 ] = IF ( CONTAINSSTRING( SELECTEDVALUE('GL Department Accounts-Stack'[GL Department]), "COGS %" ), [Total Actual MTD], SUMX ( FILTER ( SUMMARIZE( 'GL Department Accounts-Stack', 'GL Department Accounts-Stack'[Accounts Group], 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID], 'GL Department Accounts-Stack'[GL Department] ), NOT CONTAINSSTRING('GL Department Accounts-Stack'[GL Department],"COGS %") ), [Total Actual MTD] GET ERROR AFTER THIS ) VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] VAR totalactualMTD = [Total Actual MTD] VAR Cgphomeofficeincome = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41, ALL ( 'GL Department Accounts-Stack' ) ) VAR CGPEXPENSES = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42, ALL ( 'GL Department Accounts-Stack' ) ) VAR CgpHomeOffice = ((CGPEXPENSES*-1)+CGPhomeofficeincome) VAR totalrevenue = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 1, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalcogs = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalpayrollexpense = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 10, ALL ( 'GL Department Accounts-Stack' ) ) VAR totaloperatingexpenses = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 15, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalexternalexpense = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 21, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalrentexpense = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 27, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalotherexpesnsesincome = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 32, ALL ( 'GL Department Accounts-Stack' ) ) VAR mercsalesGolfOperation = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40 && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 104, ALL ( 'GL Department Accounts-Stack' ) ) VAR mercsalesFBCatering = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 40 && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 105, ALL ( 'GL Department Accounts-Stack' ) ) VAR cogsGolfOperation = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4 && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 10, ALL ( 'GL Department Accounts-Stack' ) ) VAR cogsFBcateringBanquet = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 4 && 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] = 12, ALL ( 'GL Department Accounts-Stack' ) ) VAR totalexternalexpensefinal = ( totalexternalexpense + CgpHomeOffice ) VAR grossprofit = ( totalrevenue - totalcogs ) VAR grossprofitpercentage = DIVIDE ( grossprofit, totalrevenue, 0 ) VAR revpercentage1 = DIVIDE ( totalpayrollexpense, totalrevenue, 0 ) VAR clublevelEbitdar = ( grossprofit - totalpayrollexpense - totaloperatingexpenses ) VAR revpercentage2 = DIVIDE ( clublevelEbitdar, totalrevenue, 0 ) VAR corporateEbitdar = ( clublevelEbitdar - totalexternalexpensefinal ) VAR revpercentage3 = DIVIDE ( corporateEbitdar, totalrevenue, 0 ) VAR corporateEbitda = ( corporateEbitdar - totalrentexpense ) VAR netincome = ( corporateEbitda - totalotherexpesnsesincome ) VAR cogsgolfoperper = DIVIDE ( cogsGolfOperation, mercsalesGolfOperation, 0 ) VAR cogsfbbanquertper = DIVIDE ( cogsFBcateringBanquet, mercsalesFBCatering, 0 ) VAR xxx = SWITCH ( selectvalue, 48, CgpHomeOffice, 17, grossprofit, 18, grossprofitpercentage, 29, revpercentage1, 43, clublevelEbitdar, 44, revpercentage2, 51, corporateEbitdar, 52, revpercentage3, 57, corporateEbitda, 74, netincome, 11, cogsgolfoperper, 13, cogsfbbanquertper, totalactualMTD ) //Horizontal Report Structure VAR CalcType = SELECTEDVALUE ( 'GL Department Accounts-Stack'[Calc Type] ) // should it be a simple total or a running total? VAR DisplayDetailCode = SELECTEDVALUE ( 'GL Department Accounts-Stack'[Show Detail] ) // 1 if the detail should display and 0 if detail should be hidden VAR isSubHeaderVisible = ISFILTERED ( 'GL Department Accounts-Stack'[Stack_Dept_Name] ) //we need to know if the sub header is trying to show itself VAR ismainHeaderVisible = ISFILTERED ( 'GL Department Accounts-Stack'[Stack Accounts Group] ) //we need to know if the sub header is trying to show itself VAR AccountGroupID = SELECTEDVALUE ( 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] ) // Get the selected Account Group ID VAR reeturn = SWITCH ( TRUE (), isSubHeaderVisible = TRUE () && DisplayDetailCode = "0" && CalcType = "20" && AccountGroupID <> 37 && AccountGroupID <> 39 && AccountGroupID <> 7 && AccountGroupID <> 8 && AccountGroupID <> 13 && AccountGroupID <> 18 && AccountGroupID <> 19 && AccountGroupID <> 24 && AccountGroupID <> 25 && AccountGroupID <> 30 && AccountGroupID <> 35, BLANK (), isSubHeaderVisible = TRUE () && AccountGroupID = 37, REPT ( "A", 10 ), isSubHeaderVisible = TRUE () && AccountGroupID = 39, REPT ( "A", 10 ), //[Stack-Total Actual MTD] xxx ) RETURN reeturn ))