Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to do get a cost per item but divide function is giving wrong results.
I have an ID bridge table and added the dim[ID] columns and created a Many(dim) to One(bridge) relationship, also Many(fact) to One(bridge) relationship.
DIVIDE function: = DIVIDE(sum(fact[paid]), sum(dim1[quantity]) but instead of filtering only the ID of dim1 table, the measure sums up all rows of fact[paid].
Another measure:
|
dim1 table:
|
Thanks in advance for any info and suggestions.
Solved! Go to Solution.
Hi @td1n1z
For your question, here is the method I provided:
Here's some dummy data
"Dim"
"FACT"
Create a relationship between your two tables based on the 'ID' field.
Create a measure.
DIVIDE FUNCTION = DIVIDE(
CALCULATE(
SUM('FACT'[PAID]),
'FACT'[ID] IN VALUES('Dim'[ID]) && 'FACT'[ID] = MAX('FACT'[ID])
),
CALCULATE(
SUM(Dim[quantity]),
'Dim'[ID] IN VALUES('FACT'[ID])
)
)
Here is the result.
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @td1n1z
For your question, here is the method I provided:
Here's some dummy data
"Dim"
"FACT"
Create a relationship between your two tables based on the 'ID' field.
Create a measure.
DIVIDE FUNCTION = DIVIDE(
CALCULATE(
SUM('FACT'[PAID]),
'FACT'[ID] IN VALUES('Dim'[ID]) && 'FACT'[ID] = MAX('FACT'[ID])
),
CALCULATE(
SUM(Dim[quantity]),
'Dim'[ID] IN VALUES('FACT'[ID])
)
)
Here is the result.
If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Firstly thank you very much for taking the time to answer my question.
What I found was that, I am almost embarrased to say this but, my fact table had a duplicate entry, and I was always checking my fact against my bridge table (which didn't have said duplicate).
Your solution gives me the same values I had with my measure so the oversight was on my end.
I'm gonna accept this as the correct answer.
THanks you very much once more.
Hi @td1n1z
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
59 |
User | Count |
---|---|
197 | |
116 | |
107 | |
77 | |
70 |