Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Aazam
Helper I
Helper I

Row is not adding in SUM

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

Aazam_0-1695643038038.png

 


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
)
 
12 REPLIES 12
tamerj1
Super User
Super User

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.

Aazam_0-1695824868670.png

 

 



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)

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

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

@Aazam 

What about this?

IMG_2298.jpeg

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

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]

Aazam_1-1695913399297.png

 




Aazam_0-1695913356858.png

 

 

@Aazam 

In the accounts group column what distinguishes the percentage values

I updated the answer above.

Again attaching the screenshot

Aazam_0-1695913469006.png

 

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

It blanks those rows, instead of excluding them from the sum

Aazam_0-1695914471123.png

 

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

 

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

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

))

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.