Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Helps sort BD and create data model

I didn't really know where to place the post to if I leave the link for them to see:

https://community.powerbi.com/t5/Desktop/Ayuda-crear-modelo-de-datos/td-p/1443161

In short, I need to keep track of logistical costs and I have 2 tables, the problem arises when I want to connect them and not be able to bring all the fields that I want without being alerted that "no connections"

-Table 1: Table with all sales orders (PV) with their respective values of each (each PV is unique)

-Table 2: logistical cost table, there can be more than 1 logistical cost (more than one line) for each PV

When generating my measurements I get the error that it does not sum the values well, or does not generate the indicated.

That's why I need your help, if I need to create another BD or more tables to better sort the information, I would appreciate your advice, I am also available so that you can advise me more privately or we can meet in a virtual meeting a little bit for velro in more detail. (contact me at wsp +569 81598377)

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    First, make sure that a relationship based on the field [PV] is created between Table 1 and Table 2. Then, you can create a measure  to get it as follows:

    Cost of per kg = 
VAR _curpv =
    MAX ( 'Table 1'[PV] )
VAR _sumofcost =
    CALCULATE (
        SUM ( 'Table 2'[costo] ),
        FILTER ( 'Table 2', 'Table 2'[PV] = _curpv )
    )
RETURN
    DIVIDE ( _sumofcost, MAX ( 'Table 1'[Kilos] ) )

    If the above one can't get the correct value, please provide your expected result and the related calculation logic. Thank you.

    Best Regards
    Rena
    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    First, make sure that a relationship based on the field [PV] is created between Table 1 and Table 2. Then, you can create a measure  to get it as follows:

    Cost of per kg = 
VAR _curpv =
    MAX ( 'Table 1'[PV] )
VAR _sumofcost =
    CALCULATE (
        SUM ( 'Table 2'[costo] ),
        FILTER ( 'Table 2', 'Table 2'[PV] = _curpv )
    )
RETURN
    DIVIDE ( _sumofcost, MAX ( 'Table 1'[Kilos] ) )

    If the above one can't get the correct value, please provide your expected result and the related calculation logic. Thank you.

    Best Regards
    Rena
    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.