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
)
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)
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]
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
)
Helpful resources
New to Fabric Survey
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
Power BI DataViz World Championships - June 2026
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
FabCon &SQLCon Highlights
Experience the highlights from FabCon & SQLCon, available live and on-demand starting April 14th.
Power BI Community Update - March 2026
Check out the March 2026 Power BI update to learn about new features.
