cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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
Super User I
Super User I

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

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



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

Proud to be a Datanaut !





Anonymous
Not applicable

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

how do I do that in copiable format?

Super User I
Super User I

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

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



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

Proud to be a Datanaut !





Anonymous
Not applicable

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

This is the simplified data structure:

 

This is the table:

DateCustIdAction
01.01.20181A
02.01.20181B
03.01.20181A
04.01.20181C
05.01.20182A
06.01.20182A
07.01.20182C
08.01.20182C
09.01.20182C
10.01.20182D

 

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    
CustIdABCDSum (A,B)
121103
223015

 

 

Community Support
Community Support

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

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:

Capture.PNG 

 

Regards,

Jimmy Tao

Highlighted
Anonymous
Not applicable

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

The problem is, that the visual is a matrix and 

Anonymous
Not applicable

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

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.

Community Support
Community Support

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

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:

Capture.PNG 

 

Regards,

Jimmy Tao

Community Support
Community Support

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

Hi DevOn99,

 

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

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors