Measure only calculating correctly when filter is selected

[aasamassa]

I am trying to have a measure which calculates the ratio between the amount invoiced (table MIPOD) and the amount scrapped (table NC tracking List) Here is the relationship diagram:

I created a measure 'M_ratio_nc' which calculates the percentage of the total cost with the invoiced cost.

The measure is :  M_ratio_nc = sum('NC Tracking List'[Total cost])/SUM(MIPOD[cInvoiced])

I want to create a table with the different part numbers and the ratio but the values I am getting are way off unless I select a specific part number on a slicer. 

This is the table that I am trying to create

This is the number that I am supposed to get. It only displays the right number when I select itemId (from table MIITEM)  in the slicer.

Could someone help me with this measure?

Thanks

[Syndicate_Admin]

The quick fix (but not the best one) is to put the direction of relationships in Booth

[aasamassa]

I first put the relationship direction to both in the MIPOD table and did not work but when I've put the direction to both in the NC Tracking table it seems to work. 

Could you explain why does it make a difference? And what wouls be a better fix?

Thank you!

[v-yiruan-msft · Accepted solution]

@Greg_Deckler @darioramadan Thanks for your contribution on this thread.

Hi @aasamassa ,

Thanks for your feedback. The direction of a relationship in Power BI determines how data filters are propagated between tables. Base on your screenshot, the MIPOD table doesn’t have a direct relationship with the NC Tracking table. When you set the relationship direction to both in the 'NC Tracking' table, filters applied to the NC Tracking table will affect the 'MIITEM' table and vice versa. And the filter will pass to the 'NC Tracking' table.  

Cross filter direction

The direction of relationships in Power BI | by sameer purohit | Medium

THE DIRECTION OF RELATIONSHIPS IN POWER BI | BI Consulting Services 

It is recommended to use star schema design principles to produce a model comprising dimension and fact tables. This involves creating relationships between your fact table (which appears to be MIPOD in your case) and your dimension tables (which appear to be MIITEM and NC Tracking in your case). The relationships would typically be one-to-many, with the “one” side on the dimension table and the “many” side on the fact table.Please note that while bidirectional relationships (direction set to both) can provide more flexibility in data analysis, they can also lead to performance issues and should be used with caution.

Apply star schema design principles

How to Relate Tables in Power BI - Zebra BI

Best Regards

[aasamassa]

Thank you for the reply and references. 

The fact table would have been the NC Tracking in this case but it would create a Many-to-many relationship with MIPOD and the measure would be broken. There are measures that use fields from both NC Tracking and MIPOD tables, but I didn't see anyway to have 1-to-many relationship.

[Greg_Deckler]

@aasamassa You more than likely have a relationship issue where your tables aren't filtering each other correctly. But, hard to be sure without more information like where everything is coming from in your visual, etc. Do you have sample data or a sample model that you can share?

[aasamassa]

Yes the tablea MIPOD and NC Tracking are linked through the table MIITEM.

MIPOD contains the PO informations, the NC Tracking contains defects informations and MIITEM is the list of all the items.

Sample MIITEM Table

itemId	descr	locId
DRIV-31-1270		MTL-H
180635.01		MTL-H
1924H		MTL-H
22043031		MTL-H
22051826		MTL-H
22051826A		MTL-H
22051844		MTL-H
22051844A		MTL-H
22051849A		MTL-H

Sample MIPOD data

pohId	lastRecvDt	descr	cInvoiced	received	cost	itemId
AG51233	########	XYZ	$880.	10	0	BLW-62-4005
AG51233	########	XYZ	$134.	10	XXX	BLW-07-2008
AG51233	########	XYZ	$134.	10	XXX	BLW-07-2008

Sample NC Tracking table

Part Number	Unitary Cost	Date closed	Total cost
CCo80.01	$457.	2024-02-00	$457.
337798	$74.12		$74.12
13288	$30.35	2024-01-00	$30.35
34358	$56.53	2024-01-00	$56.53

Would you need more info?