Sum of only specific columns in matrix along with display of other columns

[jesly_ajin]

Hi,

I need help. In the below table, the Total is the sum of the count of all responses in the matrix.

I need another column in the same matrix that calculates the sum of only columns 4 and 5.

Please help me to achieve this.

[v-jianboli-msft]

Hi @jesly_ajin ,

This issue may caused by this:

When you use Count, the ALLEXCEPT contains what you need to count.

Does your matrix visual have VM_ANC_xxxxDetailLookUp[Response] in its row? If it does not have this field, please remove it and try again.

Best Regards,

Jianbo Li

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

[jesly_ajin]

@v-jianboli-msft Thanks a lot for the response.

Truly appreciate.

Along with the above, I need the Total of all columns also to be displayed next to it.

[v-jianboli-msft · Accepted solution]

Hi @jesly_ajin ,

If you need both of these two totals, please try:

First create a new table:

For column = UNION(VALUES('Table'[Column]),{"Sum of 4 & 5"})

Output:

Then build relationship between the new table and your original table:

Then apply the measure:

Measure2 =
IF (
    SELECTEDVALUE ( 'For column'[Column] ) = "Sum of 4 & 5",
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[FileLocation], 'Table'[Name] ),
            OR ( [Column] = 4, [Column] = 5 )
        )
    ),
    SUM ( 'Table'[Value] )
)

Final output:

Best Regards,

Jianbo Li

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

[jesly_ajin]

@v-jianboli-msft 

I am trying to achieve as suggested by you, however, I am receiving the below error on the first step that is - First create a new table.

[v-jianboli-msft]

Hi @jesly_ajin ,

It seems that a circular dependency error has occurred.

Here is an article about this:

Understanding circular dependencies in DAX - SQLBI 

You may have to go under the hood to figure it out but generally Power BI does not allow two calculated columns that contain measures that are also based on that table aka circular dependancy.

Besides, there is also a simple but inconvenient way to solve this problem. You can t create a new table by entering it manually, which will also avoid the problem.

Best Regards,

Jianbo Li

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

[jesly_ajin]

@v-jianboli-msft As per your suggestion, i did manage to get out of the circular error. and create the relation as per step2. Now when I created the measure, i get the below error.

Please guide.

[v-jianboli-msft]

Hi @jesly_ajin ,

Based on your description, I have created a simple sample:

Please try:

Measure = IF(ISINSCOPE('Table'[Column]),SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER('Table',OR([Column]=4,'Table'[Column]=5))))

Final output:

Best Regards,

Jianbo Li

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