Forum Discussion
Row is not adding in SUM
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
)
- Aazam2 years ago
Helper I
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)- tamerj12 years ago
Community Champion
[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
)- Aazam2 years ago
Helper I
No, It did not work. I do not have any % symbol in my data.