Forum Discussion

capitan_maximum's avatar
capitan_maximum
New Member
8 years ago
Solved

USERELATIONSHIP issue

Hi,   I am quite new to power bi in generall and DAX specifically. By now I can solve simple issues myself, but I struggle with the following one I really hope you can help me with:   Background...
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    Hi capitan_maximum,

     

    I made a sample as your description and create some measures.

     

    costs customer1 = CALCULATE(SUM(OrderPosition[Costs]))
    Costs multiply = [Costs (stock order)]*CALCULATE(SUM(OrderPosition[Costs]),FILTER(ALL(OrderPosition),OrderPosition[OrderType] = "stock"))
    Gross Profit = SUM(OrderPosition[Revenue])-[Costs multiply]-[costs customer1]
    Costs (stock order) = 
SUMX (
    RELATEDTABLE ( Assigned ),
    Assigned [AmountAssigned]
)
    / CALCULATE (
        SUM ( OrderPosition[Amount]),
        USERELATIONSHIP ( OrderPosition[OrderPositionID], Assigned[OrderPositionStockID] )
)

    Then we can get the result as below.

     

    For more details, please check the pbix as attached. If that doesn’t meet your requirement, kindly share your sample data and the excepted result to me.

     

    https://www.dropbox.com/s/htmojepzj7nqs8j/USERELATIONSHIP%20issue2.pbix?dl=0

     

    Regards,

    Frank

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

Hi capitan_maximum,

 

I made a sample as your description and create some measures.

 

costs customer1 = CALCULATE(SUM(OrderPosition[Costs]))
Costs multiply = [Costs (stock order)]*CALCULATE(SUM(OrderPosition[Costs]),FILTER(ALL(OrderPosition),OrderPosition[OrderType] = "stock"))
Gross Profit = SUM(OrderPosition[Revenue])-[Costs multiply]-[costs customer1]
Costs (stock order) = 
SUMX (
    RELATEDTABLE ( Assigned ),
    Assigned [AmountAssigned]
)
    / CALCULATE (
        SUM ( OrderPosition[Amount]),
        USERELATIONSHIP ( OrderPosition[OrderPositionID], Assigned[OrderPositionStockID] )
)

Then we can get the result as below.

 

For more details, please check the pbix as attached. If that doesn’t meet your requirement, kindly share your sample data and the excepted result to me.

 

https://www.dropbox.com/s/htmojepzj7nqs8j/USERELATIONSHIP%20issue2.pbix?dl=0

 

Regards,

Frank