Multiplying calculated column values

[JohnD2]

Hi, i have created two calculated column in a table.

Now i want to multiply those values, but i get a circular error.

It is a simple Price * Quantity multiplication, where the values in the row needs to be multiplied.

What can i do to get the output in column result?

table1:

Price(calculated)  Quantity(calculated)   result (calculated)

1                             10                                 10

2                              11                                22

3                              12                                36

4                             13                                  52  

5                              14                                 70

JohnD2

[Daniil · Accepted solution]

Would you mind sharing the formulas used to calculate Price and Quantity? Multiplying two calculated columns should not give you an error by itself.

In any case, you can try using variables for the Result column, like so:

Result =
VAR PriceVar = (your Price column formula goes here)
VAR QuantityVar = (your Quantity column formula goes here)
RETURN PriceVar * QuantityVar

This way, the Result column will not depend on either Quantity or Price columns, so if you don't strictly need them, you can get rid of them and save memory.

[Anonymous]

I'm very new to PowerBi and I'm self-learning. I think I have a similar issue and I don't know how to solve it.

I have a table of data. My first column is a date and then I have created a column "Num of groups" that uses the formula DISTINCTCOUNT because I wanted to count how many groups I have for the same date. This works. 

Then, I created another column "Num of students per group" in which I used the formula IF to get a specific number based on the date in my first column.

Now, I need to multiply the column "Num of groups" by "Num of students per group". However, the data I'm getting in each row is not that but the multiplication of the number of rows that have my original table of data for the "Num of groups" by the "Num of students per group". What it is failing is the "Num of groups". This is my formula:

SUMX('Activities','Activities'[Num of groups]*'Activities'[Num of students per group])

This is the table, the result and what I expect:

Date               Num of groups     Num of students per group     Num of students (result of multiplication)       What I expect to get  

5/5/2022                 3                                  120                                            19560                                                             360

5/9/2022                 1                                  120                                            19080                                                             120

What happens is that the original data has a lot of data and the same group is repeated a lot of times and this is why I created the column 'Num of group' to have the unique count (distinctcount) for each date. The original data is in Excel and I know that I might eliminate duplicates based on groups but what I want is to create a quick report without too much manipulation of data. 

I have tried also to add VALUE() to the "Num of groups": SUMX('Activities','VALUE(Activities'[Num of groups])*'Activities'[Num of students per group]) but I get the same result.

Do you think that what I expect to get as a result of the multiplication is possible in Power BI?

Thank you!

[sdjensen · Accepted solution]

@JohnD2 - do you really need it to be a calculated column? what happens if just calculate as a measure instead like measure = SUMX( tablename, tablename[Price] * tablename[Quantity]) ?

[Daniil · Accepted solution]

Would you mind sharing the formulas used to calculate Price and Quantity? Multiplying two calculated columns should not give you an error by itself.

In any case, you can try using variables for the Result column, like so:

Result =
VAR PriceVar = (your Price column formula goes here)
VAR QuantityVar = (your Quantity column formula goes here)
RETURN PriceVar * QuantityVar

This way, the Result column will not depend on either Quantity or Price columns, so if you don't strictly need them, you can get rid of them and save memory.