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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Denis1
Frequent Visitor

Performing a calculation using the total vale for of each column for each month

I am trying to perform a calculation which will divide the total value of a column for January 2014 (spread over a number of rows i.e. there may be 4 rows of data for January 2014 and I would like to take the total for Jan 2014), by the total value of another column for January 2014 ( there may be 2 rows of data for Jan 2014 here). Can somebody give me an example of how to create this formula?

 

Basically, the sum of the column for a particular month divided by the sum of a column for the same month (and have this work for every month).

1 ACCEPTED SOLUTION

OK my formula is now:

Measure = CALCULATE(SUM('All'[Column A]),ALL('All'[Date]),VALUES('All'[Month]))/CALCULATE(SUM('All'[Calumn B]),ALL('All'[Date]),VALUES('All'[Month]))

 

I do not get an error but when I try to make a graph I get the error message "Can't display the visual. Display Details".

 

Under see details:

Error Message:

MdxScript(Model) (1, 132) Calculation error in measure 'All'[Measure]: The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.

 

View solution in original post

10 REPLIES 10
v-ljerr-msft
Microsoft Employee
Microsoft Employee

@Denis1

 

The SUM function is used to adds all the numbers in a column. So you may need to change the data type of "Column A" and "Column B" from Text to a Number type in your case. Then the SUM function should work. See my sample below.

 

I assume you have a table call "MyTestTable" like below.

t1.PNG

1. change the data type of "Column A" and "Column B" from Text to a Number type.

type.PNG

2. Use the formula below to calculate the sum of the "Column A" for a particular month divided by the sum of a "Column B" for the same month.

Measure = SUM ( MyTestTable[Column A] ) / SUM ( MyTestTable[Column B] )

3. Then you should be able to show the result of the measure for each month in the report like below.

r.PNG
Regards

LaurentCouartou
Solution Supplier
Solution Supplier

Provided you have a Dates table in your model, calculating "the sum of the column for a particular month" would look like this

 

SumOfColumn1_Jan2015:=CALCULATE( SUM( 'MyData'[Column1] )
          , Dates[Month Name]="January 2015"
)

You can do the same with the other column - I will leave the division as an exercise 🙂

Calculating the total for the current months in the selection context would look like this:

SumOfColumn1_CurrentMonths:=CALCULATE( SUM( 'MyData'[Column1] )
          , ALL(Dates)
          , VALUES(Dates[Month Name])
)

 

 

 

Thank you for helping. I am still having trouble replicating your formula. My formula looks like this and is giving me an error:

 

Measure = CALCULATE(SUM('All'[1.1. Column A]),ALL('All'[Date]),VALUES('All'[Month])/CALCULATE(SUM('All'[1.3. Colmn B]),ALL('All'[Date]),VALUES('All'[Month])))

 

Apologies if I am not grasping what you are saying correctly. Here is some more information which may help: All my data is on one sheet, I have a date column and month column.  All dates are on the first day of the month so if I can calculate for each date it would work perfectly. Again, I want to calculate all of the column entries for January 2014 divided by all of the column entries for January 2014. I need it to do this for every month for 3 years so specifying January 2014 in the formula will n ot work for me. Thank you for your help.

What error do you get?

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

You forgot a bracket before "/".

OK my formula is now:

Measure = CALCULATE(SUM('All'[Column A]),ALL('All'[Date]),VALUES('All'[Month]))/CALCULATE(SUM('All'[Calumn B]),ALL('All'[Date]),VALUES('All'[Month]))

 

I do not get an error but when I try to make a graph I get the error message "Can't display the visual. Display Details".

 

Under see details:

Error Message:

MdxScript(Model) (1, 132) Calculation error in measure 'All'[Measure]: The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.

 

Vvelarde
Community Champion
Community Champion

@Denis1

 

The error message is saying that [Calumn B] is  not a column with Number or Number Type.

 

 




Lima - Peru

Do you mean that the problem is that I have uploaded the data in this column as a text rather than a number format? As there appears to be numbers in the column when I go into the data section.

Vvelarde
Community Champion
Community Champion

Select your column and go to. Modeling and check the type



Lima - Peru

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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