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

[Denis1]

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).

[v-ljerr-msft]

@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.

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

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.

Regards

[LaurentCouartou]

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

[Denis1]

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.

[LaurentCouartou]

What error do you get?

[Denis1]

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

[LaurentCouartou]

You forgot a bracket before "/".

[Denis1 · 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.

[Vvelarde]

@Denis1

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

[Denis1]

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]

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