Forum Discussion

MacarenaGB's avatar
MacarenaGB
Helper II
5 years ago
Solved

Total is empty

I have some issues with my dax code because it doesn't sum the total, and after thinking it was because of a ISINSCOPE function, I get that the Switch I have inside doesn't sum either and I can't find how to fix it.

This is my code:

 

 

Total_Estimated_Resources =  
IF(
    ISINSCOPE('Dim_Project Database'[Program Name]) ,  
    SWITCH(
                SELECTEDVALUE('Dim_Project Database'[Program Name]),
                "Non billable" , NonBillingResourceCost,
                "Internal Project", PendingResources - NonBillingResourceCost,              
                 BLANK()
                ) 
    +
    IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())     
    ,BLANK()
)

 

 

 

 

 
What am I missing?? ðŸ¤”
Thanks!
Need Help
  • MacarenaGB's avatar
    MacarenaGB
    5 years ago

    I resolved the issues finally. 
    First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.

    After, I separated a little the code proposed by amitchandak  (thank you!) to  understand it better and here is the code: 

    VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])

VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
                [Program Name],
                "Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
                "Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),         
                BLANK()
                )
) 
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))

RETURN   Total_Cost

11 Replies

  • MacarenaGB's avatar
    MacarenaGB
    Helper II
    5 years ago

    I resolved the issues finally. 
    First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.

    After, I separated a little the code proposed by amitchandak  (thank you!) to  understand it better and here is the code: 

    VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])

VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
                [Program Name],
                "Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
                "Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),         
                BLANK()
                )
) 
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))

RETURN   Total_Cost
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    MacarenaGB , Try like

     

    Total_Estimated_Resources =  
Sumx(summarize('Dim_Project Database', 'Dim_Project Database'[Program Name],'Dim_Project Database'[Project Type], "_1", IF(
    ISINSCOPE('Dim_Project Database'[Program Name]) ,  
    SWITCH(
                SELECTEDVALUE('Dim_Project Database'[Program Name]),
                "Non billable" , NonBillingResourceCost,
                "Internal Project", PendingResources - NonBillingResourceCost,              
                 BLANK()
                ) 
    +
    IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())     
    ,BLANK()
)),[_1])

     

    refer: https://www.youtube.com/watch?v=ufHOOLdi_jk

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      MacarenaGB , try like

       

      Total_Estimated_Resources =  
Sumx(summarize('Dim_Project Database', 'Dim_Project Database'[Program Name],'Dim_Project Database'[Project Type],'Dim_Project Database'[Year],'Dim_Project Database'[Month], "_1", IF(
    ISINSCOPE('Dim_Project Database'[Program Name]) ,  
    SWITCH(
                SELECTEDVALUE('Dim_Project Database'[Program Name]),
                "Non billable" , NonBillingResourceCost,
                "Internal Project", PendingResources - NonBillingResourceCost,              
                 BLANK()
                ) 
    +
    IF(HASONEVALUE('Dim_Project Database'[Program Name]), Estimated_Resources,BLANK())     
    ,BLANK()
)),[_1])
      • MacarenaGB's avatar
        MacarenaGB
        Helper II
        5 years ago

        Hi!

        I tried that before but I can't access to the Calendar columns (it is other table)

         

  • MacarenaGB's avatar
    MacarenaGB
    Helper II
    5 years ago

    Hi! amitchandak 
    Something better but weird: 

    It is muliplying by 10 the values.


    Besides, I don't get what     _1 and [_1] make.

    Thanks for your great help!
     

     

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      MacarenaGB , _1 is a new column in summarize. [_1], when used same in sumx .

      As I can + sign near to program name. I think you need to add more columns to summarize add all columns on the row in that.

      • MacarenaGB's avatar
        MacarenaGB
        Helper II
        5 years ago

        Hi! amitchandak 

        I'm trying to understand it, but not yet 😞

        You are saying that, with the SUMX I will get the correct total and I need to create a virtual table with the proper columns to sum them correctly, right?
        In my table, I have Year and Month, but even if remove them of the matrix, the result of the column don't change.

         

  • MacarenaGB's avatar
    MacarenaGB
    Helper II
    5 years ago

    I resolved the issues finally. 
    First of all, I am not completely sure of what change fix the bug but, I created measures separated instead of using Variables in the same measure because I think it created some errors.

    After, I separated a little the code proposed by amitchandak  (thank you!) to  understand it better and here is the code: 



    VAR T_ProgramName = SUMMARIZE('Dim_Project Database', 'Dim_Project Database'[Project Type], 'Dim_Project Database'[Program Name])

VAR T_SubTotal=
ADDCOLUMNS(T_ProgramName, "Col_Estimado", [Estimated_Resources], "Col_OtrosProyectos",SWITCH(
                [Program Name],
                "Non billable" , IF(PendingResources<=0, 0, IF( PendingResources >= NonBillingResourceCost, NonBillingResourceCost, PendingResources)),
                "Internal Project", IF(( PendingResources - NonBillingResourceCost) >0,PendingResources - NonBillingResourceCost,BLANK()),         
                BLANK()
                )
) 
VAR Total_Cost = SUMX(T_SubTotal, IF([Col_OtrosProyectos] = BLANK(), [Col_Estimado], [Col_OtrosProyectos]))

RETURN   Total_Cost