Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all, hope you're well,
Can anyone please help me with this,
I got two tables with related data. I've summarized table 1 (Observations per site) like this:
And then, I have summarized the second table (Site Visits per site) like this:
Now, my question is, how can I get a new table showing me [%] of number of Obs (table 1)/ number of site visits (table 2)??.
I got to show the data in the same way per site. I have tried this, but I couldn't get the logical values I needed. However, the total percentage is correct. For instance, if you see the values in table 1 for B002=7 and in table 2 B002=22, the percentage value in the following table for B002 should be ( 7 / 22=31.8% ), but I'm getting 2.9%. In contrast, for the totals ( 348 / 832 = 41.8%) which is correct.
#Obs/Site Inspections = DIVIDE( [Total Obs], [Site Visits] )
Thanks,
Andres Torres
Solved! Go to Solution.
I created a Project dimension table and added 1:m relationships to the 3 main tables.
I made the existing relationships between fact tables inactive ( I would probably delete them but I left that for you to do if required).
I've changed the measure on the first table to be a little simpler but you'll need to check the impact on the rest of the report.
I would advise creating your own Dates table and using that instead of the built-in date features (entirely up to you).
It also looks like the CoW productivity table is really a slowly changing dimension so you might want to do something with this (I didn't investigate too much because I wanted to do the simplest thing that works to fix the initial posted question).
I'll send the pbix thru the internal mail system.
Let me know how it goes.
I created a Project dimension table and added 1:m relationships to the 3 main tables.
I made the existing relationships between fact tables inactive ( I would probably delete them but I left that for you to do if required).
I've changed the measure on the first table to be a little simpler but you'll need to check the impact on the rest of the report.
I would advise creating your own Dates table and using that instead of the built-in date features (entirely up to you).
It also looks like the CoW productivity table is really a slowly changing dimension so you might want to do something with this (I didn't investigate too much because I wanted to do the simplest thing that works to fix the initial posted question).
I'll send the pbix thru the internal mail system.
Let me know how it goes.
Hi @HotChilli ,
Thanks heaps for that. It works perfectly. I still need to understand a bit more about the relationship between the data tables, but I'm definitely going to create a new dates table like you suggested.
Many thanks,
Andres Torres
Hello @SantiagoTorres , I've got the pbix. I'm going to spend a bit of time remodelling it and I'll post back.
It looks like sensitive data so I suggest you remove the link from the above post.
can you post/link your pbix please?
I want to see the model relationships, also I think your first 2 measures can be simplified but it will depend on what the model looks like.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
79 | |
64 | |
52 | |
48 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |