Translate "++" into a value and do a calculation

[RJR71]

Hi,

I have 2 problems and I could not find a good answer. I belive it is no big deal in DAX, but I do not know how to do it.

I did a survey with MS-Forms. Thereby ++ stood for very good, 0 for neutral and -- for very bad.
Now I got a table like this:

ZielBild

++

+

0

-

--

But I need a sum as well as a mean value (Average value). If I take ++=2; +=1; 0=0; -=-1 and --=-2, the table would look like this:

ZielBild	Represents Value
++	2
++	2
+	1
-	-1
0	0
--	-2
SUM	2
Avarage	0,333

My second problem is very similar. How do I get the sum (blue row) at the end?

2. Example					SUM
++	--	-	+	+	1
2	-2	-1	1	1	1

Thank for your help in Advance.

RJR

[RJR71]

Is solved the problem in the table by replacing the "Symbols" with numbers. My problem was, that I did not see, that all columns must be "Text" to do it. I had some as a number, and so I got errors by updating the table.

But thanks to all!!! You helpt me a lot with your answers.

THANKS

RJR

[v-robertq-msft · Accepted solution]

Hi, @RJR71 

According to your two expected tables, I think you can simply achieve this using the Matrix visual in the Power BI, you can try my steps:

1. To get the first table, you can create a matrix and place it like this:

You can change the summarize type of the total row and change the name of the total row like this:

2. To get the second table, you can create a matrix and place it like this:

And you can get what you want.

You can download my test pbix file below

More info about the matrix visual in Power BI

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

You can add a calculated column which gives the numeric value for each text value.  You can do this with an 

IF or SWITCH statement e.g. 

AltColumn = SWITCH (
    [ZielBild],
    "++", 2,
    "--", -2,
    the other cases,
    0
)

Write two measures using the new column. One with SUM, one with AVERAGE.