Forum Discussion

Austinehype's avatar
Austinehype
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity 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.