Forum Discussion

Frequent Visitor

4 years ago

Solved

Help with DAX

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 F...

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

Community Champion

4 years ago

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
Frequent Visitor
4 years ago
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
  Community Champion
  4 years ago
  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
    Frequent Visitor
    4 years ago
    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...

Related content