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.
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:
Data-Modell: There are two tabels.
Tabel 1: OrderPosition
Tabel2: Name: Assigned
Relationships
What I tried so far to get the percentage of assigned products:
Costs (stock order) :=
SUMX (
RELATEDTABLE ( Assigned );
Assigned [AmountAssigned]
)
/ CALCULATE (
SUM ( OrderPosition[Amount] );
USERELATIONSHIP ( OrderPosition[OrderPositionID]; Assigned[OrderPositionStockID] )
)
I hope you can understand what I am trying to explain. This is quite hard for me to describe (especially in english). Thanks a lot!
Solved! Go to Solution.
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
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
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |