cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jonete Frequent Visitor
Frequent Visitor

Switch between % and absolute value

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

1 ACCEPTED SOLUTION

Accepted Solutions
sreenathv Member
Member

Re: Switch between % and absolute value

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

 

ItemFigureToShow
A36
B15
C18
D20
E22
F28

 

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

 

ItemFigureToShow
A25.90
B10.79
C12.95
D14.39
E15.83
F20.14

 

 

2 REPLIES 2
sreenathv Member
Member

Re: Switch between % and absolute value

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

 

ItemFigureToShow
A36
B15
C18
D20
E22
F28

 

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

 

ItemFigureToShow
A25.90
B10.79
C12.95
D14.39
E15.83
F20.14

 

 

Jonete Frequent Visitor
Frequent Visitor

Re: Switch between % and absolute value

It works great. 100%.

Thank you vey much.