Calculate average and include blanks

[Anonymous]

Hi all,

I can't figure out the correct DAX function to use for this calculation so hopefully someone can point me the right way.

Below is a screenshot of what I'm trying to achieve.  It's actually a pretty simple calculation; I need at the end of the Grand total for each category, the average total for each.  So as the examples show,  the Tables category has a grand total of 28 over a 10 month period so I need the average to be 2.8.  However, there are some months where this is blank (I createad a measure to assign a 0 value in those cases) but Power BI won't include the blank months in the calculation.  In my example Power BI shows the average as 5.6.

I'm hoping someone can un-dumb my brain please!  Thanks in advance for your help.

 

[ZEB]

I have encountered a similar scenario where Power BI's AVERAGEX()  is excluding blank and nulls from average calculation. 

Average Opportunities = AVERAGEX(

                               KEEPFILTERS(ALL(CalendarTable[Week Start Date])),

                               [Opportunities]

)

How can I modify my measure to include these blank or null values when calculating the average?

I've shared the link to the pbix file for reference. Any guidance on this would be greatly appreciated.

Thank you!

Matrix Average.pbix

@Ashish_Mathur @dsalony @SOppeneer @v-yuezhe-msft 

[SOppeneer]

@ZEB The approach I took was manually taking the sum of values divided by the count of values and blank values, so something like: SUM([values]) / ( COUNT([values]) + COUNTBLANK([values]) ). This would be in your CALCULATE function with appropriate filters. I suppose in your case this would then be the sum of [Opportunities] divided by the appropriate number of days from your CalendarTable.

[SOppeneer]

I know the post is very old (2017), but I stumbled upon it when looking into the same issue.

The difference between Excel and Power BI is that Power BI ignores blanks and zeros when calculating the average while Excel does not. There might be a better way, but I was able to solve it by manually calculating the average as the sum of values divided by the count of elements. 

Note: The column you use for the count should not include blanks, otherwise you need to ensure Power BI also counts the blanks (e.g. COUNT + COUNTBLANK). Then in Power BI it would become: SUM([values]) / ( COUNT([values]) + COUNTBLANK([values]) ).

[Ashish_Mathur]

Hi,

What formula have you written for computing the average?

[Anonymous]

Here are the different measures I have:

1)  COUNT OF CATEGORY:  IF(COUNT('MyTable'[CATEGORY])=Blank(), 0, COUNT('MyTable',[CATEGORY]) 

2) NUMBEROFMONTHS: DISTINCTCOUNT(Date_Dim(MONTHNAME_YEAR)

3) GRAND TOTAL OF CATEGORIES:  SUMX('MyTable',[COUNT OF CATEGORY]

4) AVERAGE:  CALCULATE ([GRAND TOTAL OF CATEGORIES]/NUMBEROFMONTHS)

The AVERAGE measure works if there's a 'true' value for that month/year.  It seems when I artificially add that 0 value (in the COUNT OF CATEGORY measure) it still ignores it.

Thanks!

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[Anonymous]

Hi

Unfortunately I can't share the file due to HIPAA laws.  I had to change the actual categories in my screenshot example.  This is the best data set I can give so I'm hoping someone can still help me figure this out.

[v-yuezhe-msft]

@Anonymous,

Does your original table look like below? If so, create the measure that calculate month number using this DAX: NUMBEROFMONTHS = MONTH(MAX(MyTable[Date])), then you will get correct average value.



However, if the above DAX doesn't help, please post table structure and share dummy data in your table for us to analyze.

Regards,
Lydia

[dsalony]

I have the same issue... my time period however is daily.  I tried this formula but substituted DAY func for MONTH, but it did not work.  This should be so simple to do (it is in Excel), but I cannot figure this out... in table below, I need to return the 2nd average colulmn, not the first which is what I am getting by default with PowerBI

												Day Count	10
	People Visiting the Building
Building	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10		Ave (ignore blanks)	Ave (over all 10 days)
Building A	45	49	38	44	52	31	46	48	28	37		41.80	41.80
Building B	4			7			3		9	11		6.80	3.40
Building C			1									1.00	0.10
Building D	5	6	6	7	3		4	5	6	5		5.22	4.70
Building E				3			1					2.00	0.40
Total	54	55	45	61	55	31	54	53	43	53		50.40	50.40

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.