Looking for way to create a measure to display percentage of information from related table.

[CeiBob]

Hello!

I'm a new developer of a Power BI application, which has been going quite well so far, however I have been trying to get something to connect for me.

I have a fact table with a list of all transactions at locations which I used countrows for a card to display. This card works well for me because it dynamically changes depending if im selecting one location or all locations. 

I want to divide this by the amount of customers I have at each location, which is stored in a dimensional table, which is location data (address, customer count). I have tried using a quick measure to divide it by, however it will only give me the amount of locations vs allowing me to dynamically change from all locations or one single location data. 

Is it possible to relate to this table where I get the specific row to customers to be the denominator for my dynamic fact table?

Thank you! Any help is greatly appreciated!

-Ceibob

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[CeiBob]

Thank you for your reply, lbendlin.

I am a beginner so I apologize if I am not explaining this very well.

Data has been imported from Excel sheets. All of this data is confidential so I will need to show you an example of the data. I have uploaded these example excel sheets to this.

Data Columns in fact table: (transactions)

- Transaction. These are just numbers.

- Location Code. These are codes for the location. This is linked to the next table.

- Product Version. This is linked to a version table.

- Qty. Transaction quantity.

Example: 

Transaction	Location Code	Product Code	Product Version	Qty
1	1	1	1.5	5
2	3	4	1.7	5
3	4	1	1.3	5
4	3	2	1.5	5
5	4	3	1.4	4
6	5	4	1.7	5
7	2	7	1.7	5
8	2	3	1.7	4
9	5	3	1.4	5
10	1	2	1.3	5

Location table:

- Location Code. This is linked to transactions (single to many)

- Location. Name of location.

- Address.

- Customer count. This is in our table because we have a changing set of customers which we do not want to create another table for, just using a number instead.

Example:

Location Code	Location	Address	Customer Count
1	Main Office	123 Fake St	311
2	Kelowna	234 Fake Ave	15
3	Vernon	345 Fake Cr	18
4	Calgary	456 Fake HWY	20
5	Vancouver	567 Rainy Dr	25

Version table:

- Product Code. (linked to Transaction Product Code)

- Product Name. (name)

- Latest version.

Example:

Product Code	Product Name	Latest Version
1	Device 713	1.7
2	Device 313	1.7
3	Device 513	1.7
4	Device 653	1.8
5	Device A13	1.7
6	Device 213	1.8
7	Device X13	2.1

Breaking this down:

- We sell a version of a product to a customer and keep track of that, and this version can be later upgraded. 

- We would like to keep track of how many products need to be upgraded. We mean to create a measure that shows compliance or perhaps a power query which compares the information from the fact table with the version table information.

- We also want to compare the customers to transactions in percentage. I am having trouble correlating the information from my fact able (sum of transactions for location) vs the customer count in location table. 

I am used to using a SQL database, and correlating these tables is usually very easy, but with Power BI it seems I am misunderstanding how to correlate this so I can get some good results.

Thank you, and please let me know if you need any further information.

-Ceibob

[lbendlin]

The process is very similar to SQL.  You have your tables classified as dimensions and facts, and then you wire them together accordingly

from there you can create the required report visuals and calculations.

Can you indicate the expected outcome based on your sample data?

[CeiBob]

Thanks again! 

I have set this up similarly myself. I noticed that I could get this information to relate in the table when I add the column data to a table, however relating it other ways seems to be difficult.

Ie, making a separate table displaying devices which need to be updated, or making a card which shows % of transactions per customer which is affected by selecting location information in a slicer. 

I have tried using the divide function like this, but I get errors:

Measure = DIVIDE(COUNTROWS(FILTER('Transactions','Transactions'[Location Code]')),Location[Customer Count])

It seems that the customer count cannot be referenced this way.

When summarizing in a unique table which versions are not latest versions, I am trying to come up with a plan so I can get information from this. 

Should I prep this using power query a bit more or should this be more of a power pivot thing to correct these issues?

You are a ton of help so far, thank you.

-Ceibob

[lbendlin]

1. Your data model needs to match your business premise/scenario

2. Check if you "can get there from here"  by following the arrows.  For example you cannot get from Products to Locations with the current data model.  (And you shouldn't normally need to. But if you do, change the arrow from Locations to Transactions to be bidirectional.  And then ask yourself again if you really need to do that.)

Once you sort out your data model the measure queries will be easier.

[CeiBob]

Thank you for the advice. I'll look into the relationships. 

I'll do what I can over the weekend to see if I can make this work. I may add to this on Monday! 

Have a great weekend!

-Ceibob

[CeiBob]

I created a few examples and tested them out and was not able to reference this information at all except in a table, yet I need it in a card in a summarized fashion. I have tried various relationship configurations, many to many, and structured relationships into various types. I heard that the star method was recommended for this, and even testing that, I cannot get these numbers to relate.

With the information above, if I were to have table chart set up with the required information, is it possible to display a card showing the results for: "Measure = DIVIDE(COUNTROWS(FILTER('Transactions','Transactions'[Location Code]')),Location[Customer Count])"? This always shows an error no matter the relationship between the tables. I have also tried referencing the info from the table using List.Contains, RELATED, and RELATEDTABLE. I have also tried to set this up by merging tables as well, which did not succeed as I could still not reference the information. For the version info, I have also tried using power query to add a custom column referencing if the version is less than latest version with a true / false result and it caused an error and would not reference that info no matter what I tried.

I have seen this is possible in other peoples examples but it's left out how they related this information and therefore I have not been able to make this work at all.  There must be some simple setting I am missing. This is super easy information to relate in SQL yet PowerBI has made this extremely difficult for me to figure out.

Any help would be appreciated. I've been exhausting my resources to figure this out.