Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Merging two tables having different level of granularities and calculating average

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
RegionCountry/ClustersCategoryAverageScoresUniqueKey
UKIAllCulture3.67UKIAll
UKIAllCommunication3UKIAll
LATAMAllCulture3.33LATAMAll
LATAMAllCommunication3LATAMAll
MLEMEABeneluxCulture3.67MLEMEABenelux
MLEMEABeneluxCommunication4MLEMEABenelux
MLEMEAIberiaCulture3.33MLEMEAIberia
MLEMEAIberiaCommunication2.5MLEMEAIberia

 

Table 2
RegionCountryCategoryAverageScoresUniqueKey
MLEMEASpainCulture3MLEMEAIberia
MLEMEAPortugalCulture2MLEMEAIberia
MLEMEANetherlandsCulture1MLEMEABenelux
MLEMEABelgiumCulture1MLEMEABenelux
LATAMBrazilCulture3LATAMAll
LATAMColombiaCulture2LATAMAll
LATAMMexicoCulture4LATAMAll
LATAMChileCulture2LATAMAll
UKIUnited KingdomCulture2UKIAll
UKIIrelandCulture2UKIAll

 

Table 3: Merged Tabel 1 & 2 using Union-DAX
RegionCountry/ClustersCategoryAverageScoresUniqueKeyFrom 
UKIAllCulture3.67UKIAllTable1
UKIAllCommunication3UKIAllTable1
LATAMAllCulture3.33LATAMAllTable1
LATAMAllCommunication3LATAMAllTable1
MLEMEABeneluxCulture3.67MLEMEABeneluxTable1
MLEMEABeneluxCommunication4MLEMEABeneluxTable1
MLEMEAIberiaCulture3.33MLEMEAIberiaTable1
MLEMEAIberiaCommunication2.5MLEMEAIberiaTable1
MLEMEASpainCulture3MLEMEAIberiaTable2
MLEMEAPortugalCulture2MLEMEAIberiaTable2
MLEMEANetherlandsCulture1MLEMEABeneluxTable2
MLEMEABelgiumCulture1MLEMEABeneluxTable2
LATAMBrazilCulture3LATAMAllTable2
LATAMColombiaCulture2LATAMAllTable2
LATAMMexicoCulture4LATAMAllTable2
LATAMChileCulture2LATAMAllTable2
UKIUnited KingdomCulture2UKIAllTable2
UKIIrelandCulture2UKIAllTable2

 

Many Thanks in advance.

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1610090012183.png

 

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:

v-robertq-msft_1-1610090012187.png

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1610090012183.png

 

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:

v-robertq-msft_1-1610090012187.png

 

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
Super User
Super User

@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
Not applicable

Thanks for response.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.