Forum Discussion
jaryszek
9 months agoSuper User
How to get costs for Specific Group within Bridge Model Design
Hello Guys, Power BI model: Dim_Group (GroupSK) → Bridge_ResourceGrouping (GroupSK, ResourceSK, ValidFrom, ValidTo) → Dim_Resources (ResourceSK) → Fact_Costs (ResourceSK, Date, CostAmount). ...
- 9 months ago
In your pbix, Fact_Costs[CostAmount] was of text data type, so I used SELECTEDVALUE as that didn't require any data transformation or doing something like SUMX/VALUE. Really, you can use any function or measure where I used SELECTEDVALUE, which is the direction you are already going in your question. E.g.
SUM if Fact_Costs[CostAmount] is a number format:
Costs_Number by Group = CALCULATE( SUM( Fact_Costs[CostAmount_Number] ), Bridge_ResourceGrouping )Same output but leaving column as text:
Costs_Text by Group = CALCULATE( SUMX( Fact_Costs, VALUE(Fact_Costs[CostAmount_Text]) ), Bridge_ResourceGrouping )Or, here is a calc for resource average, to get away from very simple SUM examples:
Resource Cost Avg = CALCULATE( DIVIDE( SUM(Fact_Costs[CostAmount_Number]), COUNTROWS(Dim_Resources) ), Bridge_ResourceGrouping )Table of above measures with Dim_Group[Group Name]:
added resource count to help validate the avg measure
Ashish_Mathur
9 months agoSuper User
Hi,
If there are no duplicates in the ResourceSK column of the Bridge_ResourceGrouping table, then try this approach
- In PQ, merge the Bridge_ResourceGrouping table into the Fact_Costs table to fetch the GroupSK column from there
- Create a Many to One relationship from the Fact_Cost table to the Dim_Group table
- jaryszek9 months agoSuper User
Hi,
I am on DirectLake not using power query.