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

Multiplying calculated column values

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

 

2 ACCEPTED SOLUTIONS
Daniil
Kudo Kingpin
Kudo Kingpin

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.

View solution in original post

sdjensen
Solution Sage
Solution Sage

@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]) ?

/sdjensen

View solution in original post

3 REPLIES 3
Clara_Rodriguez
Regular Visitor

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
Solution Sage
Solution Sage

@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]) ?

/sdjensen
Daniil
Kudo Kingpin
Kudo Kingpin

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors