Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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? 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |