Measure Column does not Total correct

[Jaco1965]

I need the Total to add the Column value, please help

[MFelix]

Hi @Jaco1965 ,

Being you measure a multiplication of values you need to use a SUMX formula should look something like this:

Inv_Amnt = SUMX(Work_Detail; Work_Detail[Work_Hrs] * R_ClassRate[Rate])

Be awar that you may need to adjust the parameters of the measure.

Regards,

MFelix

[Jaco1965]

Some more info

I use the 2 tables to calculate a measure, the red one. But i do need the totals to show correct below the table

[Jaco1965]

I also tried this

[MFelix]

Hi @Jaco1965 ,

Believe that this is an issue regarding with the way you have your model / relationship between tables, can you share a sample file or a mockup or explain how the model is setup? (relationships, data, fields to be consider etc.)

Regards,

MFelix

[Jaco1965]

Hi Mr Felix

Here is my file , It would be very helpfull if you can assist

https://www.dropbox.com/s/vz2mgy0wg6p8tmd/REACH%20Updated.pbix?dl=0

[MFelix]

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]

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

[Jaco1965]

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 · Accepted solution]

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]

Hi MrFelix

You are the man. Thanks

[MFelix]

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]

I also tried this

[Jaco1965]

I am still doing something wrong