Forum Discussion

jaryszek's avatar
jaryszek
Super User
9 months ago
Solved

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). ...
  • MarkLaf's avatar
    MarkLaf
    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