Forum Discussion
Help
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
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.
4 Replies
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.
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.
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
