Obtain Simple Average instead of Weighted Average

[Anonymous]

Hi!

I need that the following table visual displays the simple average instead of the weighted average:

The simple average should just be 3.9 in the first column and 36.7 in the second one.

Context:

The result displayed by the Total row is the Weighted Average because the region of Dakar has more opportunities in it than the regions of Bangkok and Amman. This means that in the first column the value 9.7 from Dakar has more weight than the other regions, because the region of Dakar has more information than the other two regions.

It is required that the simple average should be displayed. 

The data model looks like this (notice the direction of the relationships):

In the first table, the column 'Draft to Submitted' is a KPI that calculates the average value for a certain KPI Code. The DAX for this measure is:

Draft to Submitted =

CALCULATE(

AVERAGE('Opportunity KPI'[KPI Value]) ,

'Opportunity KPI'[KPI Code] = "DRAFT-SUBMITTED"

)

Then the table simply displays this KPI by Region Code from the Region table. The total is the weighted average and the simple average is required in this case.

I am not sure if this is possible or if I am doing something wrong regarding the measure or the structure of the data model, but any help on this would be much appreciated.

Thanks for your time!

[ryan_mayu · Accepted solution]

@Anonymous 

maybe try something like below

=if(hasonevalue(region),[Draft to Submitted], averagex(values(region),[Draft to Submitted])

[Anonymous]

Thank you so much! This works

[ryan_mayu]

you are welcome