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