Average of Percent

[Anonymous]

Hello Community  -  I am trying to get an average at the bottom total to make sense.   In other words, if one were to add up the percentages in a column, and divide by the number of rows, it would be correct.  

.04

.09

.03

In simple terms, these three rows would average to .0533     And to use that logic below, the sum of the average 898.2977 / 112 would equal 8.0205  but I get a different result.     Any ideas?   I do need the formula to account for filters that may be added.  

Average Discount =

VAR AvgDiscount =

AVERAGEX(

FILTER(

'AllOpps-Products',

'AllOpps-Products'[Discount] = BLANK() || 'AllOpps-Products'[Discount] >=0

),

'AllOpps-Products'[Discount]

)

RETURN

IF(

HASONEVALUE( 'Date Table'[Month] ),

AvgDiscount,

(

AVERAGEX(

VALUES( 'Date Table'[Month] ),

AvgDiscount

)

)

)

[Anonymous]

Hi @Anonymous,

It should help us clarify your scenario and test to coding formula if you please share some dummy data with a similar data structure and expected results.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Try like

Average Discount =
VAR AvgDiscount =
Sumx(
FILTER(
'AllOpps-Products',
'AllOpps-Products'[Discount] = BLANK() || 'AllOpps-Products'[Discount] >=0
),
'AllOpps-Products'[Discount]
)
RETURN
divide
(
Countx(
VALUES( 'Date Table'[Month] ),
AvgDiscount
) , AvgDiscount
)

or

Average Discount =
VAR AvgDiscount =
Averagex(
FILTER(
'AllOpps-Products',
'AllOpps-Products'[Discount] = BLANK() || 'AllOpps-Products'[Discount] >=0
),
'AllOpps-Products'[Discount]
)
RETURN
divide
(
Countx(
VALUES( 'Date Table'[Month] ),
AvgDiscount
) , AvgDiscount
)

[Anonymous]

@amitchandak     Something is still very weird and I can't understand what Power Bi is doing.   Your formula is included below, along with another one and then also just the average using the built in Power Bi one to average that discount column.   All return the same results for these three rows...and it matches in Excel.  

However.....if I add just ONE additional row...things get weird.   In the second visual below you can see I have added one row by adding "open" to the filter.    If you add those 4 rows with a calculator (or Excel) and divide by 4 you will get 11.75% which is the correct number because I want to include all 4 rows.   The 0% needs to be counted  (not ignored).     I have no idea why Power Bi is saying  12.591%