cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimmyDQN Regular Visitor
Regular Visitor

AverageIF - Based on one category, combined with another average

Hi all,

Our developer (small time company) is on vacation and since I am not able to do calculated columns myself as we use SSAS cube, I am hoping one of you can help me. 
The thing is - I have a Hierarchy in Table 1 containing Continent, Country and City. In Table 2, I have Person ID and Table 3, I have hours registered. 

I would like to do a measure (preferably a calculated column, but I am not able to do this, as I don't have access to the SQL server) where I am calculating the following: an Average based on City and an Average compared to the others in the same city. I would LOVE for it to be in the same table / visualization. Is this even doable without any developer having to create a new column for me?

 

See an example below

ContinentCountryCityPerson IDHours registeredAverageBasedonCityAverage compared to group in City
AsiaChinaGuangzhou76875,00%
AsiaChinaGuangzhou8108125,00%
EUGermanyBerlin23475,00%
EUGermanyBerlin454125,00%
EUSwedenStockholm655100,00%
NAUSANew York121811,11%
NAUSANew York341822,22%
NAUSANew York561833,33%
NAUSALA404040100,00%
NAUSANEw York106018333,33%




1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jiascu-msft
Microsoft

Re: AverageIF - Based on one category, combined with another average

Hi @JimmyDQN,

 

Is it a Direct Query connection? I have updated the attachment. You can try it again.

 

 

Best Regards,
Dale

Community Support Team _ Dale
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

15 REPLIES 15
Highlighted
JimmyDQN Regular Visitor
Regular Visitor

Re: AverageIF - Based on one category, combined with another average

Anyone? 🙂

Microsoft v-jiascu-msft
Microsoft

Re: AverageIF - Based on one category, combined with another average

Hi @JimmyDQN,

 

Can you share a dummy sample, please? The data and its structure are the critical parts to make a formula. For example, 

1. How did these data tables connect?

2. Which columns have duplicates?

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JimmyDQN Regular Visitor
Regular Visitor

Re: AverageIF - Based on one category, combined with another average

@v-jiascu-msft

 

Hi, 

 

I have an attachment above. Or do you mean the data model? 🙂

Microsoft v-jiascu-msft
Microsoft

Re: AverageIF - Based on one category, combined with another average

Hi @JimmyDQN,

 

Yes, it's the data model. You have at least three tables. I need to know the connections among them.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JimmyDQN Regular Visitor
Regular Visitor

Re: AverageIF - Based on one category, combined with another average

Pic.PNG

 

@v-jiascu-msft would this help? 🙂

Microsoft v-jiascu-msft
Microsoft

Re: AverageIF - Based on one category, combined with another average

Hi @JimmyDQN,

 

The calculated column [AverageBasedonCity] is easy and it should be in the table 'geography'.

 

AverageBasedonCity =
CALCULATE (
    AVERAGE ( 'hours registered'[Hours] ),
    ALLEXCEPT ( 'geography', 'geography'[city] )
)

The other one could be in the table 'hours registered'. 

 

 

Average compared to group in City =
[hours] / RELATED ( 'geography'[AverageBasedonCity] )

If they aren't correct, please provide a dummy sample.

 

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JimmyDQN Regular Visitor
Regular Visitor

Re: AverageIF - Based on one category, combined with another average

@v-jiascu-msft Would it be possible to do this without calculated columns but only measures?

Microsoft v-jiascu-msft
Microsoft

Re: AverageIF - Based on one category, combined with another average

Hi @JimmyDQN,

 

Yes, I just follow your first post. It could be like below. If not, please share a sample.

 

AverageBasedonCity =
CALCULATE (
    AVERAGE ( 'hours registered'[Hours] ),
    ALLEXCEPT ( 'geography', 'geography'[city] ),
    ALL ( person )
)
Average compared to group in City =
SUM ( [hours] ) / [AverageBasedonCity]

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JimmyDQN Regular Visitor
Regular Visitor

Re: AverageIF - Based on one category, combined with another average

It does not work unfortunatly, how do I attach a file?

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)