Power BI Exchange

Expand all | Collapse all

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 10 days ago
    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

    Posted 6 days ago
    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
    Business Intelligence
    ------------------------------



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

    Posted 6 days ago
    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

    Posted 5 days ago
    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
    Business Intelligence
    ------------------------------



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

    Posted 4 days ago
    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 4 days ago
    Edited by Tom Ellis 4 days ago
    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
    ------------------------------