cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CAMA_UK_18
Frequent Visitor

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 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):

 

ClaireAMAdams_0-1644931119653.png

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

 

Thanks

 

1 ACCEPTED SOLUTION
CAMA_UK_18
Frequent Visitor

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

View solution in original post

6 REPLIES 6
CAMA_UK_18
Frequent Visitor

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

@CAMA_UK_18 Awesome to hear! Well done!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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.

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.