Merging two tables having different level of granularities and calculating average

[Anonymous]

Hi All,

Need 1 help on below query.

I have 2 different entities which I have merged into 1 table in order to show the average in a Spider Web chart basis on Category. After merging I want the visuals to be interacted Country-wise.

Requirement: I want to filter Country – “United Kingdom” & Category – “Culture”. I think the average should be 2.84 (=(3.67+2)/2) (highlighted in  Table 1 & Table 2).  But I am getting value as 2.56 because it is consider Ireland also (as per Table 3) (=(3.67+2+2)/2)

Is there any way or suggestions to achieve the value as expected. Sample Data below:

Table 1
Region	Country/Clusters	Category	AverageScores	UniqueKey
UKI	All	Culture	3.67	UKIAll
UKI	All	Communication	3	UKIAll
LATAM	All	Culture	3.33	LATAMAll
LATAM	All	Communication	3	LATAMAll
MLEMEA	Benelux	Culture	3.67	MLEMEABenelux
MLEMEA	Benelux	Communication	4	MLEMEABenelux
MLEMEA	Iberia	Culture	3.33	MLEMEAIberia
MLEMEA	Iberia	Communication	2.5	MLEMEAIberia

Table 2
Region	Country	Category	AverageScores	UniqueKey
MLEMEA	Spain	Culture	3	MLEMEAIberia
MLEMEA	Portugal	Culture	2	MLEMEAIberia
MLEMEA	Netherlands	Culture	1	MLEMEABenelux
MLEMEA	Belgium	Culture	1	MLEMEABenelux
LATAM	Brazil	Culture	3	LATAMAll
LATAM	Colombia	Culture	2	LATAMAll
LATAM	Mexico	Culture	4	LATAMAll
LATAM	Chile	Culture	2	LATAMAll
UKI	United Kingdom	Culture	2	UKIAll
UKI	Ireland	Culture	2	UKIAll

Table 3: Merged Tabel 1 & 2 using Union-DAX
Region	Country/Clusters	Category	AverageScores	UniqueKey	From
UKI	All	Culture	3.67	UKIAll	Table1
UKI	All	Communication	3	UKIAll	Table1
LATAM	All	Culture	3.33	LATAMAll	Table1
LATAM	All	Communication	3	LATAMAll	Table1
MLEMEA	Benelux	Culture	3.67	MLEMEABenelux	Table1
MLEMEA	Benelux	Communication	4	MLEMEABenelux	Table1
MLEMEA	Iberia	Culture	3.33	MLEMEAIberia	Table1
MLEMEA	Iberia	Communication	2.5	MLEMEAIberia	Table1
MLEMEA	Spain	Culture	3	MLEMEAIberia	Table2
MLEMEA	Portugal	Culture	2	MLEMEAIberia	Table2
MLEMEA	Netherlands	Culture	1	MLEMEABenelux	Table2
MLEMEA	Belgium	Culture	1	MLEMEABenelux	Table2
LATAM	Brazil	Culture	3	LATAMAll	Table2
LATAM	Colombia	Culture	2	LATAMAll	Table2
LATAM	Mexico	Culture	4	LATAMAll	Table2
LATAM	Chile	Culture	2	LATAMAll	Table2
UKI	United Kingdom	Culture	2	UKIAll	Table2
UKI	Ireland	Culture	2	UKIAll	Table2

Many Thanks in advance.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you want to get the average of the [AverageScores] to 2.56 which not include “Ireland”, I think you can achieve this using the visual filter, you can follow my steps:

1. Create a table chart to place all columns in ‘Table 3’ and two Silcers to place [Region] and [Category].
2. Click on the table chart and set the visual filter to exclude “Ireland” in [Country], like this:

You can also use one measure to get the average value:

Average =

AVERAGEX(FILTER(ALLSELECTED('Table 3'),[Country/Clusters]<>"Ireland"),[AverageScores])

Then click on this measure and set the measure format to “Decimal number” in the measure tool, like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , How are you displaying , In this case it should not

You can have measure divide(Sum(Table[AverageScores]),Count(Table[AverageScores]))

Just check in UI how many rows it shows when filter for UK and culture

[Anonymous]

Thanks for response.

It is giving me 2 when i am filtering UK & Culture instead of 2.84.