Help with dax expressions

[ashmitp869]

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 :

[danextian]

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... 

[ashmitp869]

Sample file attached.

https://github.com/suvechha/samplepbi/blob/main/samplecom.pbix

[danextian]

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
        )
    )

[ashmitp869]

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
            
        
        )
    )

[danextian · Accepted 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
        )
    )