Forum Discussion

Aazam's avatar
Aazam
Icon for Helper I rankHelper I
2 years ago

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

 


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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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
    )

    • Aazam's avatar
      Aazam
      Icon for Helper I rankHelper 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)
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        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
        )