Row totals identical to column total

[paulb1985]

Dear all,

There is probably a very easy solution to this issue, but I cannot find it after 1.5h of browing forum posts...

In PowerQuery (for Excel, not PowerBI, but should be the same principle), I have a file with spend data that includes columns like year, spend category, and supplier name, as well as the amount of spend.

The model contains the data table plus tables per dimension (supplier, category, and so on) which are linked in a many-to-one way.

The main table, "Full Data" is the basis for the dimension tables with unique values for "Categories", "Calendar", and the various "Vendors" tables. On a side note, you will see several vendors tables because I had to several steps of cleaning (trim, clean, remove double spaces, capitalise each word, etc.).

In addition, there is a measure to sum up all spend: CALCULATE(SUM('Full Data'[Sum invoice value (EUR)])).

There is no issue calculating the absolute spend per category per year:

The calculation of % spend by category across years also works:

 

What does not work, is calculating the spend per supplier per year - here, I get the grand total per year shown in every single supplier row.

Can someone please tell me what I have been missing?

Many, many thanks in advance, this is a great community!

Paul

 

[johnt75 · Accepted solution]

There's no relationship between 'Vendors Cleaned Manually', which appears to be the active table, and 'Full Data'. If the Supplier column in your matrix visual is coming from 'Vendors Cleaned Manually' you would need to add a 1-to-many relationship to 'Full Data'

[paulb1985]

@johnt75  thanks very much - that did the trick!

[paulb1985]

Aha, thanks very much. Have to try that once I get home. I thought the linking of one-to-many relationships in the various supplier queries would (indirectly) take care of that. Will let you know in case it works!