Help with DAX

[CAMA_UK_18]

I wonder if someone could spare some time to help me with writing a measure? It seems easy logically but I can't get it to work!

I've simplified the query onto excel: I have a Sales Table and a Facts Table (cost of logistics for each customer). I've linked the two tables via a one:many relationship from the Logistics Table to the Sales Table.

Using SUMX and Related, I've successfully written some simple dax (but not simple for me!) that returns the correct result in the matrix for sales x Logistics fee %. However, no matter what measure I try to write to pull in the fixed fee per customer, I get the total of all fixed fees on every line (highlighted red on the blue table):

How should my measure be written? It's driving me mad!

Thanks

[CAMA_UK_18 · Accepted solution]

I reworked my relationships and it works just fine now. Argh! Thanks for your help, though.

[TheoC]

@CAMA_UK_18 Awesome to hear! Well done!

[TheoC]

Hi @CAMA_UK_18 

I think your fields for Fixed Fee might be set to SUM.  On the Matrix visual, can you click the down arrow on the Fixed Fee column and select Don't Summarise or select Max?

Hopefully this helps!

Theo

[CAMA_UK_18]

Hi @TheoC ! I don't see 'Don't summarise' and selecting 'Max' gives me the same value on each row still, albeit the max value rather than the total. It's as though the relationship isn't working, but if that was the case, I don't think my other measure would work either.

[TheoC]

Hi @CAMA_UK_18 

Thanks for letting me know. Can you try use LOOKUPVALUE to see if that works? https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Usually, if RELATED isn't working, it could be a relationship issue. If it is, LOOKUPVALUE will hopefully resolve this (as long as there is one Fixed Fee per Customer ID).

Hope this helps.

Theo

[CAMA_UK_18]

Hi @TheoC 

In the sales table, I added a calculated column: 

TestFixedFee = RELATED(RefTable_Logistics_Terms_Fixed[Fixed £])

and it correctly brought in the fixed fee per customer, but of course repeating it on every single relevant row. When bringing that calculated column into the matrix, I can set it to 'max' and it shows the right value per line, but then it also shows the max value as the column total, not the sum of all the fixed costs.

Also, I was trying to avoid a calculated column and just write a measure, but perhaps I can't...