cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PS
Helper I
Helper I

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, @PS 

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, @PS 

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

amitchandak
Super User IV
Super User IV

@PS , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for response.

 

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.