cancel
Showing results 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

Helper III

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

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.

1 ACCEPTED SOLUTION
Community Support

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.

7 REPLIES 7
Community Support

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.

Helper III

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

Community Support

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.

Helper III

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.

Community Support

Hi @jesly_ajin ,

It seems that a circular dependency error has occurred.

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.

Helper III

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

Community Support

Hi @jesly_ajin ,

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

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

Announcements

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

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors