prices for nested resources

[acnt_schartner]

Hi,

we have an order confimation (KHKVKBelege). In that confirmation are positions (KHKVKBelegPositionen) with sell item numbers.

There is a ressource head table (KHKPpsRessourcenKopf) for each sell item number in the positions.

The problem is that the ressoruce head table is just the top most layer. Under that are ressource positions (KHKPpsRessourcenPositionen). A Ressource position can also be made from several buy items.

This as a connection between buy items and a ressource ref column that points to the item above. The worst nesting like this is 7 layers.

I am a bit lost here. Can someone give me some pointers?

Goal 1: In the ressource positions there is a quantity. I want that quantity for each item and each layer.

Goal 2: I want to get the average price from a purchasing table (KHKEKBelegPositionen) (same item numbers as buy items) for each item in the nesting-relationship.

I can not share this file. It contains customer-details.

[acnt_schartner]

I have managed to get the relations for the main ressourcelists right. I can see the nested ressources correctly. 

now i want to get the right amount.

For the 1st level i use

Menge (1) = IF(ISINSCOPE('KHKPpsRessourcenPositionen (2)'[Matchcode]),BLANK(),

SUM('KHKPpsRessourcenPositionen (1)'[Menge]))

This works fine. But on the second level i do not want the amounts to summarize on the first level.

Second level amount is

IF(ISINSCOPE('KHKPpsRessourcenPositionen (3)'[Matchcode]),BLANK(),

SUM('KHKPpsRessourcenPositionen (2)'[Menge]))

Yellow marks should be blank

[Anonymous]

Hi @acnt_schartner 

From years of experience in the data modeling business one thing I can tell you right away is this: change your data model. What you have right now is a sure way to fail miserably. Here's something to get you started: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

[acnt_schartner]

Hi,

i am trying to find a better way. But the resourcelists are nested in our erp-system and i can not change that.