Forum Discussion

Austinehype's avatar
Austinehype
Helper I
5 years ago

Table dax

Hi!   I have a table with revenues and different type of products. The problem is there are some products where the value is 0 hence I would like to take the average revenue value of that product i...
Need Help
Austinehype's avatar
Austinehype
Helper I
5 years ago

For Example:

 

Product    Revenue 

A                3432

B                 0 take the average of B

C                 754

D                 0 take the average of D

A                 0 take the average of A

B                  246

C                  1245

D                  321

 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Austinehype 

     

    See if this works for you.

    A) As a column in your data table:

     

     using this code to create a calculated column:

     

    Revenue or average = 
VAR AverCalc = CALCULATE(AVERAGE('DataTable'[Revenue]), 
                FILTER('DataTable', 
                'DataTable'[Product] = EARLIER('DataTable'[Product])))
RETURN
    IF('DataTable'[Revenue] = 0, AverCalc, 
        'DataTable'[Revenue])

     

     

    B) As a measure:

     

    which is done with:

    1) Sum of revenue :

     

     

    Sum of Revenue = SUM('DataTable'[Revenue])

     

     

    2) Replace 0 with average:

     

     

    Replace 0 with Average = 
VAR AverCalc = CALCULATE(AVERAGE('DataTable'[Revenue]), 
                ALLEXCEPT('DataTable', 'DataTable'[Product])) //Calculates the average revenue by product
RETURN
IF(ISINSCOPE('DataTable'[Product]), 
    IF([Sum of Revenue] = 0, AverCalc, 
        [Sum of Revenue])) //Replaces 0 revenue with corresponding average.