Forum Discussion
Solved
Help with dax expressions
Hi , Can any one help me with dax measures.I need to calculate Open Commitments against this Cost Category : Labour, Plant & Equipments etc. The data I have like :
Change your formula to below or you can simply just hide that blank category row.
Open Commitments_ = VAR _labour = SUM ( CommittedCostSummary[LaborOpenCommittedCost] ) VAR _allowance = SUM ( CommittedCostSummary[AllowanceOpenCommittedCost] ) VAR _costcategory = SELECTEDVALUE ( CostCategory[Cost Category] ) RETURN IF ( NOT ( HASONEVALUE ( CostCategory[Cost Category] ) ), _labour + _allowance, SWITCH ( TRUE (), _costcategory = "Labour", _labour, _costcategory = "Allowance", _allowance ) )
Try this:
Open Commitments_ =
VAR _labour =
SUM ( CommittedCostSummary[LaborOpenCommittedCost] )
VAR _allowance =
SUM ( CommittedCostSummary[AllowanceOpenCommittedCost] )
VAR _costcategory =
SELECTEDVALUE ( CostCategory[Cost Category] )
RETURN
IF (
ISBLANK ( _costcategory ),
_labour + _allowance,
SWITCH (
TRUE (),
_costcategory = "Labour", _labour,
_costcategory = "Allowance", _allowance
)
)
Hi danextian Thanks the expressions worked and also getting TOTAL. But how to omit the Last line
Becoz the TOTAL is duplicating , I don't need to show the last line instead show the TOTAL.
Open Commitments = VAR _labour =
SUM ( CommittedCostSummary[LaborOpenCommittedCost] )
VAR _allowance =
SUM ( CommittedCostSummary[AllowanceOpenCommittedCost] )
VAR _fees =
SUM ( CommittedCostSummary[FeesOpenCommittedCost] )
VAR _fom =
SUM ( CommittedCostSummary[FOMRentedEquipmentOpenCommittedCost] )
VAR _sub =
SUM ( CommittedCostSummary[SubcontractOpenCommittedCost] )
VAR _sup =
SUM ( CommittedCostSummary[SuppliesOpenCommittedCost] )
VAR _mat =
SUM ( CommittedCostSummary[MaterialsOpenCommittedCost] )
VAR _equi =
SUM ( CommittedCostSummary[ConstructionEquipmentOpenCommittedCost] )
VAR _unde =
SUM ( CommittedCostSummary[UndefinedOpenCommittedCost] )
VAR _costcategory =
SELECTEDVALUE ( CostCategory[Cost Category] )
RETURN
IF (
ISBLANK( _costcategory ),
_labour + _allowance + _fees + _fom + _sub + _sup + _mat + _equi + _unde,
SWITCH (
TRUE (),
_costcategory = "Labour", _labour,
_costcategory = "Allowance", _allowance,
_costcategory = "Fees", _fees,
_costcategory = "FOM Rented Equipment", _fom,
_costcategory = "Subcontractors", _sub,
_costcategory = "Construction Supplies", _sup,
_costcategory = "Project Materials", _mat,
_costcategory = "Plant & Equipment", _equi,
_costcategory = "Undefined", _unde
)
)Change your formula to below or you can simply just hide that blank category row.
Open Commitments_ = VAR _labour = SUM ( CommittedCostSummary[LaborOpenCommittedCost] ) VAR _allowance = SUM ( CommittedCostSummary[AllowanceOpenCommittedCost] ) VAR _costcategory = SELECTEDVALUE ( CostCategory[Cost Category] ) RETURN IF ( NOT ( HASONEVALUE ( CostCategory[Cost Category] ) ), _labour + _allowance, SWITCH ( TRUE (), _costcategory = "Labour", _labour, _costcategory = "Allowance", _allowance ) )