Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 :
Solved! Go to Solution.
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/1447...
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
)
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
68 | |
57 | |
55 | |
36 | |
34 |
User | Count |
---|---|
76 | |
73 | |
48 | |
45 | |
43 |