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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MacarenaGB
Helper II
Helper II

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

 

 

 

 

Screenshot_15.png
 
What am I missing?? 🤔
Thanks!
1 ACCEPTED SOLUTION
MacarenaGB
Helper II
Helper II

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

View solution in original post

11 REPLIES 11
MacarenaGB
Helper II
Helper II

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

@MacarenaGB , Thanks for posting the solution. Kudos to you....

MacarenaGB
Helper II
Helper II

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

 

MacarenaGB
Helper II
Helper II

Hi! @amitchandak 
Something better but weird: 

MacarenaGB_0-1612975191724.png

It is muliplying by 10 the values.


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

Thanks for your great help!
 

 

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

Hi! @amitchandak 

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

MacarenaGB_0-1613032322232.png

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.

 

amitchandak
Super User
Super User

@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

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

Hi!

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

MacarenaGB_1-1613038190450.png

 

@amitchandak any solution?

Thanks!

Hi, @MacarenaGB 

I think you can change the columns that you circled in the DAX formula to

'Dim_Project Database'[Year],'Dim_Project Database'[Month]

v-robertq-msft_0-1613547561628.png

 

 

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

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