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

Highlighted
##

Anonymous

Not applicable

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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?

10 REPLIES 10

Highlighted
##

**Did I answer your question? Mark my post as a solution!**

PattemManohar

Super User I

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

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

11-20-2018
04:59 AM

@Anonymous 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 Super User!

Highlighted
##

Anonymous

Not applicable

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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?

Highlighted
##

**Did I answer your question? Mark my post as a solution!**

PattemManohar

Super User I

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

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

11-20-2018
05:08 AM

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

Proud to be a Super User!

Highlighted
##

Anonymous

Not applicable

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

Highlighted
##

Anonymous

Not applicable

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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
##

Anonymous

Not applicable

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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.

Highlighted
##

v-yuta-msft

Community Support

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

Highlighted
##

v-yuta-msft

Community Support

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- 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

Announcements

Visit our Community Blog for articles, guides, and information created by fellow community members.

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Top Solution Authors

User | Count |
---|---|

342 | |

216 | |

120 | |

68 | |

63 |

Top Kudoed Authors

User | Count |
---|---|

388 | |

291 | |

153 | |

82 | |

79 |