Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Continent | Country | City | Person ID | Hours registered | AverageBasedonCity | Average compared to group in City |
Asia | China | Guangzhou | 7 | 6 | 8 | 75,00% |
Asia | China | Guangzhou | 8 | 10 | 8 | 125,00% |
EU | Germany | Berlin | 2 | 3 | 4 | 75,00% |
EU | Germany | Berlin | 4 | 5 | 4 | 125,00% |
EU | Sweden | Stockholm | 6 | 5 | 5 | 100,00% |
NA | USA | New York | 1 | 2 | 18 | 11,11% |
NA | USA | New York | 3 | 4 | 18 | 22,22% |
NA | USA | New York | 5 | 6 | 18 | 33,33% |
NA | USA | LA | 40 | 40 | 40 | 100,00% |
NA | USA | NEw York | 10 | 60 | 18 | 333,33% |
Solved! Go to Solution.
Hi @JimmyDQN,
Is it a Direct Query connection? I have updated the attachment. You can try it again.
Best Regards,
Dale
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
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
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
@v-jiascu-msft Would it be possible to do this without calculated columns but only measures?
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
It does not work unfortunatly, how do I attach a file?
Hi @JimmyDQN,
You can upload the file to a cloud drive like OneDrive, GoogleDrive then share the link here or send me a message through Messages. Please mask the sensitive parts first.
Best Regards,
Dale
@v-jiascu-msft https://drive.google.com/open?id=1pGmwarQb1xM9o0HeCj3T096vksUGuomO here is the link.
I couldnt, for some reason, make a realtionship from Geography. I had to export some data, as it is on a SSAS cube.
Hi @JimmyDQN,
Please check out the demo in the attachment. There are solutions both of "calculated column" and "measure".
Though I don't know why there are duplicates in the Geography table which is thought to be a dimension table, the solutions work.
BTW, we can create a many-to-many relationship now.
Best Regards,
Dale
I get an error trying to download this. But, I cannot make columns as I am on an SSAS cube.
Hi @JimmyDQN,
Is it a Direct Query connection? I have updated the attachment. You can try it again.
Best Regards,
Dale
Hi,
sorry for the late reply.
It is correct that I am using direct query. I will try to look at this tomorrow 🙂
Anyone? 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |