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 ) )
Hi ashmitp869
As always, please provide a workable sample data and not an image and your expected result from that - how it is to be computed, etc. Please refer to this post: https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
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 ) )