Forum Discussion

jaryszek's avatar
jaryszek
Icon for Super User rankSuper 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). ...
Need Help
  • 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

     

MarkLaf's avatar
MarkLaf
Icon for Super User rankSuper User
9 months ago

You can enforce the filter from the many side to the one side with the bridge table, either as a filter argument in calculate or a simple conditional to use as a measure filter.

 

As in:

 

Bridge measure filter

BridgeFilter = IF( NOT ISEMPTY( Bridge_ResourceGrouping ), 1 )

Add to visual filter and set to is not blank

 

Bridge filter argument

CostAmount by Group = CALCULATE( SELECTEDVALUE( Fact_Costs[CostAmount] ), Bridge_ResourceGrouping )

 

jaryszek's avatar
jaryszek
Icon for Super User rankSuper User
9 months ago

This is interesting but seems to be workig only with slicers. I remember your solution from my previous posts. 

CostAmount by Group = CALCULATE( SELECTEDVALUE( Fact_Costs[CostAmount] ), Bridge_ResourceGrouping )


How this would work? 
Why there is a selected value? From slicer?

It would work only with?:

CostAmount by Group = CALCULATE(Sum(Fact_Costs[CostAmount]), Bridge_ResourceGrouping )


Best,
Jacek