Forum Discussion

Jaco1965's avatar
Jaco1965
Frequent Visitor
7 years ago
Solved

Measure Column does not Total correct

I need the Total to add the Column value, please help  
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Jaco1965 ,

     

    In my measure I use the ";" as a separator this is related with regional settings, change it by "," and see if you are abble to use the measure.

    Total_Inv_value = SUMX(Work_Detail , [Inv_Amnt])

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
7 years ago

Hi Jaco1965 ,

 

This as to do with the context of your measures and the way you have setup the database with the link between the Resources values and the tasks and work details (nothing is incorrect) and the measures need to be readjust to give the right result.

 

Create the following meaaures:

Already created


Inv_Amnt = 
    sum(Work_Detail[Work_Hrs]) * Sum(R_ClassRate[Rate])

New one
Total_Inv_value = SUMX(Work_Detail;[Inv_Amnt])

Then use the last one to have your final result:

As you can see changing the way the calculation is made the context changes and the result is correct when compared with the red highlited value.

 

Again noting wrong with your setup, I tried to make a summarized table, or a group by to use in your measure but would only create more complexity on the measures.

 

Regards,

MFelix

gustavoleo's avatar
gustavoleo
Advocate II
7 years ago

Check out this blog post by SQLBI

DAX 101: Summing values for the total

https://rebrand.ly/correcttotal

 

MFelix wrote:

Hi Jaco1965 ,

 

This as to do with the context of your measures and the way you have setup the database with the link between the Resources values and the tasks and work details (nothing is incorrect) and the measures need to be readjust to give the right result.

 

Create the following meaaures:

Already created


Inv_Amnt = 
    sum(Work_Detail[Work_Hrs]) * Sum(R_ClassRate[Rate])

New one
Total_Inv_value = SUMX(Work_Detail;[Inv_Amnt])

Then use the last one to have your final result:

As you can see changing the way the calculation is made the context changes and the result is correct when compared with the red highlited value.

 

Again noting wrong with your setup, I tried to make a summarized table, or a group by to use in your measure but would only create more complexity on the measures.

 

Regards,

MFelix


DAX 101: Summing values for the total (@FerrariAlberto) https://www.sqlbi.com/articles/summin...

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi gustavoleo ,

     

    Your post is interesting however in the post there is a simple relation between dates and sales and is related with the granularity of the information, in the case of Jaco1965  is model is not simple because he uses two dimension tables to get the calculation made and between two of them there is a bidirectionality that changes the ways tables interact.

     

    That's why the easier way is to create a separate measure thar uses the first one as a parameter, giving context in different places for the total row.

     

    Regards,

    MFelix

  • Jaco1965's avatar
    Jaco1965
    Frequent Visitor
    7 years ago

    Hi MrFelix

     

    I tried that but I get error with the sumx, it odes not give me option to select the measure from list

     

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Jaco1965 ,

     

    In my measure I use the ";" as a separator this is related with regional settings, change it by "," and see if you are abble to use the measure.

    Total_Inv_value = SUMX(Work_Detail , [Inv_Amnt])

     

    Regards,

    MFelix

  • Jaco1965's avatar
    Jaco1965
    Frequent Visitor
    7 years ago

    Hi MrFelix

     

    You are the man. Thanks