How do I sum 2 variables for each month, then take the AVG of those SUMs (not values) for the month?

[alicek]

I am not sure how to explain this correctly - plase let me know if you need more information. 

To simplify, I have a data table that looks like this:

Date                       Material                              Value

January 2016          Number of books              130,000

January 2016          Number of eBooks            400,000

February 2016        Number of books              140,000

February 2016        Number of eBooks            410,000

I'd like to know the average of total materials each month -- so here is how I would calculate this manually.

Step 1: Add each month totals together.

      (130,000 + 400,000 = 530,000)

      (140,000 + 410,000 = 550,000)

Step 2: Average the monthly totals.

      (530,000 + 550,000) / 2 = 540,000

However, PowerBI continues to add them all together and take the average of each of the four variables. 

      (130,000 + 400,000 + 140,000 + 410,000) / 4 = 270,000

I tried to create this new column: 

Sum_Materials = CALCULATE(SUM(Query1[actual]), OR(Query1[measuretitle]="Number of books", Query1[measuretitle]="Number of eBooks")) and then to choose the "Average" from the drop-down when I put it into a card visual. However, this did not change BI's methodology from above. 

I also tried to create this new measure: Sum_2_MainBranch_Visitors = AVERAGE(Query1[Sum_MainBranch_Visitors])

Still, no luck!

Do you all have any suggestions of how I can write this calcuation correctly?

[PSBR]

Running total       = CALCULATE(sum'Table_Name'(value),
                             FILTER(ALL(Table_Name),
                             Table_Name[Date]<= MAX(Table_Name[Date])))

for the first requrement you have to create running total like above measure. 

[v-frfei-msft]

Hi @alicek,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[Ashish_Mathur]

Hi,

So in your Table visual, there will be one figure for each year.  Am i correct?  Also, is the Date in the first column an actual Date entry.  Share the actual data layout which i can paste in an Excel workbook.

[alicek]

Hi Ashish! thank you. I've copied and pasted a  table below. I use the Calendar Month column, and yes, it is a date variable. 

Department	Measure Title	Calendar Month		Actual	Category
Public Library	Circulation of eBooks and eMedia	1/1/2017		130,000	ACT
Public Library	Circulation of physical books and materials	1/1/2017		400,000	ACT
Public Library	Circulation of physical books and materials	2/1/2017		410,000	ACT
Public Library	Circulation of eBooks and eMedia	2/1/2017		140,000	ACT

The visual should be a card that is the average of the sum of each month. 

So for other measures which I don't have to sum, I drag "Calendar Month" onto the Visual Filter and just say January to February, for example, and it automatically averages them and shows that average on the card. But if I do that with these measures, it averages all four values instead of summing the month and then averaging those two sums. 

Anything else I can add?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[supersql12]

Thank you so much!!!! I downloaded your pbix file followed your example and it worked for my scenario - i've been searching hi and low and trying so many ways!!!!

[v-frfei-msft]

Hi @alicek,

I made one sample using your data.

1. Enter the data and create a calculated column.

Month = FORMAT(Table1[Calendar Month],"mmm")

2. Create the measures as bleow.

Circulation of eBooks and eMedia = CALCULATE(SUM(Table1[Actual]),Table1[Measure Title]= "Circulation of eBooks and eMedia")

Circulation of physical books and materials = CALCULATE(SUM(Table1[Actual]),Table1[Measure Title]="Circulation of physical books and materials")

countbooks = CALCULATE(DISTINCTCOUNT(Table1[Month]),Table1[Measure Title]="Circulation of physical books and materials")

countebook = CALCULATE(DISTINCTCOUNT(Table1[Month]),Table1[Measure Title]="Circulation of eBooks and eMedia")

average-ebook = [Circulation of eBooks and eMedia]/[countebook]

averagebook = [Circulation of physical books and materials]/[countbooks]

For more details, please check the pbix as attached.

Regards,

Frank