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

# Add column to matrix visual

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

01-14-2019 03:38 AM

Hello,

I am working with data like this:

Division | DivisionNumber | Type | Amount |

P | 1 | Available | 100 |

M | 2 | Available | 400 |

V | 3 | Requested | 200 |

P | 1 | Available | 300 |

P | 4 | Allocated | 800 |

M | 5 | Allocated | 200 |

P | 1 | Available | 100 |

V | 3 | Requested | 300 |

V | 2 | Allocated | 600 |

Out of this table I created a matrix visual:

Now I would like to **add a column to that matrix **which sums up the two columns "Available" and "Requested" for each row.

Is that possible? If not, how could I mainpulate the data before making the visual (I think I would need to add calculated rows to solve this but that is not possible, right?).

Thanks in advance.

Solved! Go to Solution.

Accepted Solutions

## Re: Add column to matrix visual

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

01-14-2019 04:15 AM

Hi @Mana

This might be a bit of an unnecessarily intricate solution, certainly compared to @Zubair_Muhammad's, but you could try:

**1.** Create a new, table DisplayTable with one column with the values of your Type column plus one for the additional column "Available+Requested". Create a relationship between DisplayTable[Type] and Table1[Type] (Table1 is your current table)

**2. **Place DisplayTable[Type] in the columns of your matrix visual (the rows are the same as you have now)

**3. **Create this measure:

Measure = SWITCH ( TRUE (), SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ), Table1[Type] IN { "Available", "Requested" } ) )

**4. **Place the measure in values of your matrix visual

## Re: Add column to matrix visual

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

01-14-2019 06:35 AM

Most likely nothing wrong I hadn't tested it.

Try this small change:

Measure = SWITCH ( TRUE (), SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ),DisplayTable[Type] IN { "Available", "Requested" } ) )

All Replies

## Re: Add column to matrix visual

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

01-14-2019 03:53 AM - edited 01-14-2019 03:55 AM

You can add a MEASURE but i think it will repeat for each Column in the Matrix....

Measure = CALCULATE ( SUM ( Table1[Amount] ), [Type] = "Available" ) + CALCULATE ( SUM ( Table1[Amount] ), [Type] = "Requested" )

Another way could be to use Power Query

First pivot the "Type" Column...Then you can add the "Available" and "requested" Columns

Not unpivot again

Check this article as well

http://www.excelnaccess.com/adding-calculated-row-calculated-items-using-power-query/

## Re: Add column to matrix visual

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

01-14-2019 04:15 AM

Hi @Mana

This might be a bit of an unnecessarily intricate solution, certainly compared to @Zubair_Muhammad's, but you could try:

**1.** Create a new, table DisplayTable with one column with the values of your Type column plus one for the additional column "Available+Requested". Create a relationship between DisplayTable[Type] and Table1[Type] (Table1 is your current table)

**2. **Place DisplayTable[Type] in the columns of your matrix visual (the rows are the same as you have now)

**3. **Create this measure:

Measure = SWITCH ( TRUE (), SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ), Table1[Type] IN { "Available", "Requested" } ) )

**4. **Place the measure in values of your matrix visual

## Re: Add column to matrix visual

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

01-14-2019 05:53 AM - edited 01-14-2019 05:53 AM

## Re: Add column to matrix visual

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

01-14-2019 06:35 AM

Most likely nothing wrong I hadn't tested it.

Try this small change:

Measure = SWITCH ( TRUE (), SELECTEDVALUE ( DisplayTable[Type] ) IN ALL ( Table1[Type] ), SUM ( Table1[Amount] ), CALCULATE ( SUM ( Table1[Amount] ),DisplayTable[Type] IN { "Available", "Requested" } ) )

## Re: Add column to matrix visual

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

01-14-2019 11:20 PM

It worked, thanks so much!

Is there any possibility to add two more columns based on the column we just calculated?

First one should be: Allocated - (Available+Requested)

Second one: (Available+Requested) / Allocated * 100

## Re: Add column to matrix visual

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

01-15-2019 05:21 AM - edited 01-17-2019 04:19 PM

Yes, we can do it by following the same logic but the more columns you add, the less sense it makes to do it this way and the more with one different measure for each calculation. The code is getting overly long now, I would think.

You'd need to add the new column names to the DisplayTable. I've used the exact copy of how you've described the operations. You can update that if you want with something shorter but then you'd have to update the corresponding comparisons in the code as well. The new DisplayTable would now look like:

Type

Available |

Requested |

Allocated |

Available+Requested |

Allocated - (Available+Requested) |

(Available+Requested) / Allocated * 100 |

and we need to make some changes in the measure:

Measure 2 = VAR _CurrentColumn = SELECTEDVALUE ( DisplayTable[Type] ) RETURN SWITCH ( TRUE (); _CurrentColumn IN ALL ( Table1[Type] ); SUM ( Table1[Amount] ); _CurrentColumn = "Available+Requested";

CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] IN { "Available"; "Requested" } ); _CurrentColumn = "Allocated - (Available+Requested)";

CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] = "Allocated" ) - CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] IN { "Available"; "Requested" } ); _CurrentColumn = "(Available+Requested) / Allocated * 100"; 100*DIVIDE ( CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] IN { "Available"; "Requested" } ); CALCULATE ( SUM ( Table1[Amount] ); DisplayTable[Type] = "Allocated" ) ) )

I've interpreted (Available+Requested) / Allocated * 100 to be

100*( (Available+Requested) / Allocated)

If this is not the case you can update the code with the correct calculation

## Re: Add column to matrix visual

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

01-16-2019 05:13 AM

Wow, thanks again, this works absolutely fine.

I don't need any other columns, so there is nothing to complain about.

I really appreciate your help, thank you!