Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
11 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
    11 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