topic Re: Row is not adding in SUM in DAX Commands and Tips

Row is not adding in SUM

Aazam — Mon, 25 Sep 2023 11:59:34 GMT

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. But I want to calculate it is there any solution for that?

Home Office Value is not added to in total

DAX

MYDAX = var selectvalue = SELECTEDVALUE('GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]) var totalactualMTD = [Total Actual MTD] var myhomeofficeincome=CALCULATE([Total Actual MTD],'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41, ALL('GL Department Accounts-Stack')) var MYEXPENSES=CALCULATE([Total Actual MTD],'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,ALL('GL Department Accounts-Stack')) var MyHomeOffice = ((MYEXPENSES*-1)+myhomeofficeincome) var xxx= switch( selectvalue, 48,MyHomeOffice, totalactualMTD )

Re: Row is not adding in SUM

tamerj1 — Tue, 26 Sep 2023 16:37:48 GMT

Hi Aazam

please try

MYDAX =
SUMX (
VALUES ( 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] ),
VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
VAR totalactualMTD = [Total Actual MTD]
VAR myhomeofficeincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MYEXPENSES =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MyHomeOffice = myhomeofficeincome - MYEXPENSES
VAR xxx =
SWITCH ( selectvalue, 48, MyHomeOffice, totalactualMTD )
RETURN
xxx
)

Re: Row is not adding in SUM

Aazam — Wed, 27 Sep 2023 14:29:01 GMT

It did work but I have some % values, so when I SUMX all values then it will sum my percentage values in total as well.

here is my complete Dax where I implemented your solution.

[Combine-Stack-Total Actual MTD -- Testing Aazam ] = SUMX ( VALUES ( 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] ), VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] VAR totalactualMTD = [Total Actual MTD] VAR myhomeofficeincome = CALCULATE ([Total Actual MTD],'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,ALL ( 'GL Department Accounts-Stack' )) VAR MYEXPENSES = CALCULATE ( [Total Actual MTD], 'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42, ALL ( 'GL Department Accounts-Stack' )) VAR MYHomeOffice = myhomeofficeincome - MYEXPENSES 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, MYHomeOffice, 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)

Re: Row is not adding in SUM

tamerj1 — Wed, 27 Sep 2023 17:13:27 GMT

Aazam

[Combine-Stack-Total Actual MTD -- Testing Aazam ] =
SUMX (
FILTER (
VALUES ( 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID] ),
NOT CONTAINSSTRING ( 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID], "%" )
),
VAR selectvalue = 'GL Department Accounts-Stack'[GL_Dept_Group_Sort_ID]
VAR totalactualMTD = [Total Actual MTD]
VAR myhomeofficeincome =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 41,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MYEXPENSES =
CALCULATE (
[Total Actual MTD],
'GL Department Accounts-Stack'[GL_Acct_Group_Sort_ID] = 42,
ALL ( 'GL Department Accounts-Stack' )
)
VAR MYHomeOffice = myhomeofficeincome - MYEXPENSES
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, MYHomeOffice,
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
)

Re: Row is not adding in SUM

Aazam — Wed, 27 Sep 2023 20:21:27 GMT

No, It did not work. I do not have any % symbol in my data.

Re: Row is not adding in SUM

tamerj1 — Thu, 28 Sep 2023 14:50:44 GMT

Aazam

What about this?

If not then what do you have? What distinguishes the percentage values from others? Any flag column?

Re: Row is not adding in SUM

Aazam — Thu, 28 Sep 2023 15:03:26 GMT

It is a template I created. These fields are coming from the same table GL Department Accounts-Stack but the column is Accounts Group ('GL Department Accounts-Stack'[GL Departments]

Re: Row is not adding in SUM

tamerj1 — Thu, 28 Sep 2023 15:03:03 GMT

Aazam

In the accounts group column what distinguishes the percentage values

Re: Row is not adding in SUM

Aazam — Thu, 28 Sep 2023 15:04:41 GMT

I updated the answer above.

Again attaching the screenshot

Re: Row is not adding in SUM

tamerj1 — Thu, 28 Sep 2023 15:10:49 GMT

Aazam

[Combine-Stack-Total Actual MTD -- Testing Aazam ] =
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], "%" )
),
"Rest of the code"
)

Re: Row is not adding in SUM

Aazam — Thu, 28 Sep 2023 15:21:54 GMT

It 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 )

Re: Row is not adding in SUM

tamerj1 — Mon, 02 Oct 2023 17:01:05 GMT

Aazam

Please 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]
)
)

Re: Row is not adding in SUM

Aazam — Mon, 02 Oct 2023 17:45:09 GMT

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 ))