Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Anonymous
Not applicable

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.