Forum Discussion

td1n1z's avatar
td1n1z
Regular Visitor
2 years ago
Solved

divide function / measure provides wrong values

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) relationshi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

 

 

 

 

  • td1n1z's avatar
    td1n1z
    Regular Visitor
    2 years ago

    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.