- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Adding a Total Column to a Matrix Visual that ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

DevOn99

Frequent Visitor

Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
04:46 AM

I have a table that has (simplified) the columns CustID, Action, Date

I then have a matrix visual that has Action as Columns, CustID AS Rows and Count(Action) as Values.

Now I need an extra column that sums only some of the Columns, not all like the Total. In Excel this is =SUMME(B11:K11), where K12 and K13 are the columns not to be summed.

Is this possible in PBI Matrix?

9 REPLIES 9

PattemManohar

Super User

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
04:59 AM

@DevOn99 Please post the sample data (copiable format) and expected output to understand your scenario in detail. It will be helpful to provide an accurate solution.

Proud to be a **Datanaut !**

DevOn99

Frequent Visitor

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
05:03 AM

how do I do that in copiable format?

PattemManohar

Super User

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
05:08 AM

@DevOn99 I mean the sample data that can be copied. Not like screenshots or image format.

Proud to be a **Datanaut !**

DevOn99

Frequent Visitor

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
05:27 AM

This is the simplified data structure:

This is the table:

Date | CustId | Action |

01.01.2018 | 1 | A |

02.01.2018 | 1 | B |

03.01.2018 | 1 | A |

04.01.2018 | 1 | C |

05.01.2018 | 2 | A |

06.01.2018 | 2 | A |

07.01.2018 | 2 | C |

08.01.2018 | 2 | C |

09.01.2018 | 2 | C |

10.01.2018 | 2 | D |

Expected outcome (I did this as a Matrix in PBI, but cannot see a way to add the Sum(A,B) column):

PIVOT Values are Count(Action) | |||||

Action | |||||

CustId | A | B | C | D | Sum (A,B) |

1 | 2 | 1 | 1 | 0 | 3 |

2 | 2 | 3 | 0 | 1 | 5 |

v-yuta-msft

Community Support Team

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-20-2018
11:45 PM

Hi DevOn99,

You you create measures using DAX formula like pattern below:

A = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "A")) B = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "B")) C = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "C")) D = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "D")) Sum(A, B) = CALCULATE(COUNT(Table1[Action]), FILTER(ALLEXCEPT(Table1, Table1[CustId]), Table1[Action] = "A" || Table1[Action] = "B"))

This result is like this:

Regards,

Jimmy Tao

DevOn99

Frequent Visitor

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018
01:58 AM

The problem is, that the visual is a matrix and

Highlighted
##

DevOn99

Frequent Visitor

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018
03:17 PM

you made a table visual. I need a matrix visual that has Action in columns and Custid and Date (in real solution, there are more criteria like country, gender etc) in rows. rows are drillable.

So, if I have a matrix that filters on rows AND columns, I guess it is not possible to have extra colums for measures that only filter on row criteria, but is independant from column criteria. Or is there a solution?

My solution now is:

I added an extra column in Query Editor that is always 1. Then pivoted the column Action (using the always 1 column as value) and use the new columns (one for each distinct value in Action) in data section. Leave column section empty. Then I can add measures to the data section and they get also new columns.

v-yuta-msft

Community Support Team

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018
01:53 AM

Hi DevOn99,

I'm afraid the matrix in your post couldn't be realized because the SUM(A,B) is not a value in Action column, as a workaround, you can drag measure sum(A,B) above the value field lile below:

Regards,

Jimmy Tao

v-yuta-msft

Community Support Team

Re: Adding a Total Column to a Matrix Visual that only sums spcific columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018
05:55 PM

Hi DevOn99,

Have you solved your issue currently? If you have, could you kindly mark my answer to finish this thread?

Regards,

Jimmy Tao