Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
I reworked my relationships and it works just fine now. Argh! Thanks for your help, though.
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
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |