Incorrect Subtotal

[yakolev]

Hi PBI Community,

I am trying to build a formula that multiplies two columns: sectors flown by KM (kilometers) - so I am testing the different components in the below matrix.

Sectors flown sits on a fact table and KMs on a lookup table connected via Route (e.g. Brisbane Melbourne = 1800 KMs)

The issue is that - when I drag and drop the KM column into a matrix, I get the category total for each subcategory (e.g. total KMs for the Triangle category 5,670 kms where what I really want to get is the Brisbane - Melbourne segment 1,800 kms (refer red column below).

To break down further and access each individual KM range, I have created a measure (blue below) that uses SUMX - however, it sums up each individual instance of the (in this example) the Brisbane - Melbourne segment and adds it up, which is not what I am after.

In Excel this would simply be accomplished by creating a new column - with a VLOOKUP formula that looks into a table with route pairs and returns the correct KMs for that pair (row).

EXTRA: While at it - I have a similar problem with Total Seats column above. Total seats also sits in a related and linked table. Solving the above will also help me solve this problem. However, I just wanted to point out that, interestingly - the amount showing at a row level is the GRAND TOTAL for the whole column - somewhat different to the above case where total at a row level is the CATEGORY total e.g. total for "Triangle", "Regional West", etc. However, for either case I am after the total (which is the actual KMs) at SUBCATEGORY level e.g. city pairs.

Thanks, 

[AllisonKennedy]

@yakolev  Your tables must be related with 1 to many via route, not category. See if this post helps explain: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_11.html 

[yakolev]

Hi @AllisonKennedy 

Thanks - but fact to lookup tables are related via Routes (please refer screenshot below)

The common column in Lookup table is called "One-Way Segment"  but is essentially "Route".