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
Fabric Community Update - November 2024
Find out what's new and trending in the Fabric Community.
Be one of the first to start using Fabric Databases
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Power BI Monthly Update - November 2024
Check out the November 2024 Power BI update to learn about new features.
