# Thread: Mixing percentage and absolute values in same column

1. Junior Member
Join Date
Aug 2011
Posts
6

## Mixing percentage and absolute values in same column

Hello all,

Is is possible, with MDX, to create a table with both absolute values and percentage in the same columns ?

The table should display # of transactions and amount spent (measures) per each of 3 categories (dimensions). Thus far, I have it figured out. But I also want to add a row that shows the % of people & amounts in these 3 categories across all (many more) categories.

Example:
Total transactions : 100
Total amount spent : 1000\$

My Current table:

---------+---------+----------
category | # trans | \$ spent
---------+---------+----------
food | 20 | 300\$
toys | 40 | 200\$
tools | 10 | 100\$

Now I want to add another row that shows the percentage of ( sum of all categories ) across the grand totals:
---------+---------+----------
% | 70 % | 60 %

---------+---------+----------

Is it possible to achieve this in a single query ?

This is the query, without the % :

WITH
MEMBER [Measures].[# trans] AS ........
MEMBER [Measures].[\$ spent] AS ........
MEMBER [Measures].[TOT trans] AS ......
MEMBER [Measures].[TOT spent] AS .....
SELECT
{ [Measures].[# trans], [Measures].[\$ spent] } ON COLUMNS
{ [Categories].[food], [Categories].[toys], [Categories].[tools] } ON ROWS

The row I need to add is not a category. It's would be (I think) a calculated member that sums the # trans (or \$ spent) across these 3 categories, divided by [Measures].[TOT trans], formatted as %

Any help would be greatly appreciated !  Reply With Quote

2. Junior Member
Join Date
Aug 2011
Posts
6

## I've resolved this problem, here's my solution to applying mixed formatting across rows & columns:

WITH

MEMBER [Measures].[# trans] AS ( ... )

MEMBER [Measures].[\$ spent ] AS ( ... )

MEMBER [Categories].[food] AS ( ... ),
FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "\$ spent", "\$0;(\$0);", "" )

MEMBER [Categories].[toys] AS ( ... ),
FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "\$ spent", "\$0;(\$0);", "" )

MEMBER [Categories].[tools] AS ( ... ),
FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "\$ spent", "\$0;(\$0);", "" )

MEMBER [Categories].[Total] AS ( ... )
FORMAT_STRING = IIF( [Measures].CurrentMember.Name = "\$ spent", "\$0;(\$0);", "" )

MEMBER [Categories].[% Total] AS ( ... )
FORMAT_STRING = "Percent"

SELECT
{
[Measures].[Accounts ],
[Measures].[Receivables]
} ON COLUMNS,
{
[Account.Status].[food],
[Account.Status].[toys],
[Account.Status].[tools],
[Account.Status].[Total],
[Account.Status].[% Total]
} ON ROWS

FROM
[MyCube]

And the results display as follow:

---------+---------+----------
category | # trans | \$ spent
---------+---------+----------
food.....|....20...|...300\$...
toys.....|....40...|...200\$...
tools....|....10...|...100\$...
Total....|...100...|..1000\$...
% Total..|....70%..|....60%...

Now I'm happy. Mixed formatting across rows, and across columns as well   Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•