# Power BI Exchange

View Only

## Average of a Calculeted measure across companies where the measure is not \$0

• #### 1.  Average of a Calculeted measure across companies where the measure is not \$0

Posted Apr 12, 2019 03:28 AM
I'm working on an accounting model, which consolidates figures from multiple companies.  the data is imported as monthly balances.  there are also some non-financial data points imported as monthly balances.   Also, to get total sales requires the addition of multiple accounts, to assist with this, accounts are mapped to an account tree, so that I can filter by tree branch, instead of listing each account number.  even then, I need 3 tree branches to get total sales.

For example:
dispensary sales + retail sales + other income = total sales

but dispensary sales formula is:
Dispensary Sales YTD =
TOTALYTD(CALCULATE(SUM('FactsTable'[ledgerBalance]),PLTreeMap[PL Branch] IN {"1110 Dispensary Sales"}), 'Calendar1'[DATE], "30-Jun")*-1
+
TOTALYTD(CALCULATE(SUM('FactsTable'[ledgerBalance]),PLTreeMap[PL Branch] IN {"1210 Other Dispensary Sales"}), 'Calendar1'[DATE], "30-Jun")*-1

So I need to know the average YTD Sale from all the companies included.  More than that, I need it to not include any with a \$0 YTD sales amount.  eg Sales = \$112,032,304,  companies with ytd sales is 57, so average should be 1,965,479.  There are 250 companies in the company list, but not all have data YTD.

the standard YTD formula will get me the total sales across the group.  The Average formula is an average of a column, not an average of a measure.   I could use a TOTALYTD(CALCULATE(AVERAGE(ledgerbalance)... then filter with multiple OR selections on the PL Branch column (at least 5).  Just not sure if this function includes \$0 in the count for the average.

I've tried using countrows of the Branches table, with as many filters as I can get applied to it (4 columns filtered), but there is not combination of filters that brings the list down to 57 companies.
The FILTER formula is applied to a table, but I need to filter with the results of a measure.

Any Ideas??

------------------------------
Neville Howard
Consultant
Brisbane
0732228400
------------------------------

• #### 2.  RE: Average of a Calculeted measure across companies where the measure is not \$0

Bronze Contributor
Posted Apr 15, 2019 05:01 AM
Hi Neville,

I'm not sure if I understand clearly what you need.
It looks to me that you're a bit over complicated your formula.

```Dispensary Sales YTD =

TOTALYTD(CALCULATE(SUM('FactsTable'[ledgerBalance]),PLTreeMap[PL Branch] IN {"1110 Dispensary Sales"}), 'Calendar1'[DATE], "30-Jun")*-1

+

TOTALYTD(CALCULATE(SUM('FactsTable'[ledgerBalance]),PLTreeMap[PL Branch] IN {"1210 Other Dispensary Sales"}), 'Calendar1'[DATE], "30-Jun")*-1

//could be wrtien as:
Dispensary Sales YTD =

TOTALYTD(CALCULATE(SUM('FactsTable'[ledgerBalance]),PLTreeMap[PL Branch] IN {"1110 Dispensary Sales","1210 Other Dispensary Sales"}), 'Calendar1'[DATE], "30-Jun")*-1

​```

And the best way to list your 57 companies would be to use the function Values(your companies) by making sure you include only the companies with sales >0

------------------------------
Benoit Fedit
------------------------------

• #### 3.  RE: Average of a Calculeted measure across companies where the measure is not \$0

Posted Apr 15, 2019 07:11 PM
Thanks for the hints about the formula, during early development I like to keep things obvious, and also arranged so that I can copy and paste formulas to be recycled.

To try to explain my issue...
I have a measure that will give me total Sales.  I can put that into a visual and then apply a visual level filter to only give me the 57 companies with sales.

However, I now want to know the average of those total sales.
If I change the existing measure to use Average (instead of Sum), what I get is the average based on the number of entries in the fact table.  So for a pharmacy that is only updated (by trail balance import) twice a year, it's average is Total Sales/2.   But another pharmacy which is updated each month, is Total Sales/12.
What I need is the average sales per pharmacy so: Total Sales/No of pharmacies.  Not only that but: Total Sales/No of pharmacies with sales

So I try what would work in an excel formula
Count = CALCULATE(count(vwCompany[CompCode]),[Dispensary Sales PTD] <> 0)

But that is not allowed in DAX. As filters have to be applied to a column, but I'm trying to apply a filter to a calculation.

I'm thinking I need to create a temporary table, in order to count the resulting rows, but not sure how.

thanks,

------------------------------
Neville Howard
Consultant
Brisbane
0732228400
------------------------------

• #### 4.  RE: Average of a Calculeted measure across companies where the measure is not \$0

Bronze Contributor
Posted Apr 16, 2019 08:58 AM
Hi Neville,

It would be helpful if you could attach some data with you expected result.
So from what your understand you need something like this:
This formula computes the average of the average of each company with sales >0
```Measure = AVERAGEX
(   summarize
(   Sheet1
;Sheet1[company]
;	"AVGByCompany";calculate(AVERAGE(Sheet1[sales]);Sheet1[sales]>0)
)
;	[AVGByCompany]
)​```

With the sample data below I get an average of 34.17 for company A (410/12) and 75 for company B (150/2) which gives 54.78 ((34.17+75)/2)
```company	sales
a	20
a	10
a	20
a	10
a	100
a	50
a	10
a	20
a	10
a	50
a	10
a	100
b	50
b	100
c	0
d	0
​```

------------------------------
Benoit Fedit
------------------------------

• #### 5.  RE: Average of a Calculeted measure across companies where the measure is not \$0

Posted Apr 17, 2019 06:39 PM
To illustrate the challenge using data...

What I want is the 'typical' YTD Sale amount of the pharmacies trading this year (in my data set).

The raw data goes something like this.

 Pharmacy MonthEnd Sales A 31/07/2018 25,000 A 31/08/2018 26,000 A 30/09/2018 25,500 A 31/10/2018 24,500 A 30/11/2018 26,500 A 31/12/2018 27,000 B 31/07/2018 25,500 B 31/08/2018 26,500 B 30/09/2018 25,500 B 31/10/2018 24,500 B 30/11/2018 26,500 B 31/12/2018 27,500 C 31/07/2018 - C 31/08/2018 - C 30/09/2018 - C 31/10/2018 - C 30/11/2018 - C 31/12/2018 - Average 17,250 Average Non Zero 25,875

An average of the column effectively gives you a monthly average sale, as the count is the number of records.  Even if you exclude the non trading entity C, the average is a typical month, not a typical YTD.

A Measure is used to get YTD Sales for each pharmacy:

 Total Sales A 154,500 B 156,000 C -

Making a Grand Total of \$310,500
The average I'm after is \$310,500 / 2 (count of trading entities) = 155,250

Someone has given me a formula that will get the count of entities with YTD Sales, I can then use the results of the two measure to get my YTD Average per company.  It applies a Filter to the Company Dimensions Table using the Total Sales Measure.

CALCULATE(DISTINCTCOUNT('Companies'[CompanyName]),FILTER(Sales,[Total Sales] <> 0)

Happy to receive any other suggestions.

------------------------------
Neville Howard
Consultant
Brisbane
0732228400
------------------------------

• #### 6.  RE: Average of a Calculeted measure across companies where the measure is not \$0

Posted Apr 18, 2019 12:55 AM
Edited by Tom Ellis Apr 18, 2019 01:16 AM
This is from the sample immediately above for  the Average Non NULL/BLANK=    25,875. Might need more tweaking if 0 is the default value to exclude and the YTD per Pharmacy is needed:

YTDValue = TOTALYTD(CALCULATE(AVERAGE(tblPharmacy[Sales]), ALLNOBLANKROW(tblPharmacy[Pharmacy])),tblPharmacy[MonthEnd],"Jun-30")

ALLNOBLANK(table|column)
From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist

------------------------------
Tom Ellis
BI Manager
------------------------------