Switch between % and absolute value

[Jonete]

Hello:

I have a matrix and I would like to switch between % of column total and absolute values, and show each value depending on the selection chosen by the user.

Now i'm doing it duplicating the page and showing absolute values in one page and % in the other, but I would like to know if there is any chance to do it with bookmarks (i've tried with no success) or with any other way. That way, i could earn one page and make the information more friendly.

Thanks in advance:

Jon

[Anonymous · Accepted solution]

Hello,

I don’t know if there is any standard option like that, but there is a workaround that I can explain through a small example.

Assume we have a table named “Sales” with two fields.

Item	Qty
A	36
B	15
C	18
D	20
E	22
F	28

The requirement is two show either the sales as absolute value or as percentage to total against each item.

We can achieve this functionality using the following method.

Step 1: Select “Enter Data” option and create the following table.

DisplayChoice	Flag
Absolute Value	1
Percentage	2

Step 2: Create the following measures.

Choice = SUM(Choice[Flag])

Note: If the value of Choice is 1, we will show absolute value. If choice = 2, then we will show percentage. If it’s 3, we will show blank (or the default option that you decide)

FigureToShow = 
IF( Choice[Choice]=1,
	SUM(Sales[Qty]),
IF(Choice[Choice]=2,
SUM(Sales[Qty]) / SUMX(ALL(Sales) , Sales[Qty]) *100,
BLANK() )
)

Step 3: Create a slicer using the DisplayChoice field which will have two options “Absolute Value” and “Percentage”

Based on the User’s choice, you will either get AbsoluteValue or Percentage as the result in the measure FigureToShow.

If the user choose AbsoluteValue the Matrix will show the following output

Item	FigureToShow
A	36
B	15
C	18
D	20
E	22
F	28

If Percentage is chosen, then the matrix will give the following output

Item	FigureToShow
A	25.90
B	10.79
C	12.95
D	14.39
E	15.83
F	20.14

[Jonete]

It works great. 100%.

Thank you vey much.