Forum Discussion

ccsnet's avatar
ccsnet
Frequent Visitor
2 years ago
Solved

Matching Totals Once Using Filters

Hi all,   I have a series of data which have a shared ID and I would like to show a total only once rather than matching to every line. The following is the code I have and the result... does any ...
  • ccsnet's avatar
    ccsnet
    2 years ago

    Thank you all - for now I have gone with ...

     

    This is because SUMX did not want to work and repeated the same number all the way down however I will go back but assume I could get a couple of missed numbers for now.

     

     

    Sum Project Billed Milestones = 
CALCULATE(
    SUM(Autotask_ContractMilestones_Billed[amount]),
    FILTER(
        DISTINCT(Autotask_ContractMilestones_Billed[contractID]),
        Autotask_ContractMilestones_Billed[contractID] = Autotask_Projects[contractID]
    )
)

     

     

    I will also have a look at the schema option which seems to be a good way forward too.

     

    Thanks

     

    T

ccsnet's avatar
ccsnet
Frequent Visitor
2 years ago

Thank you all - for now I have gone with ...

 

This is because SUMX did not want to work and repeated the same number all the way down however I will go back but assume I could get a couple of missed numbers for now.

 

 

Sum Project Billed Milestones = 
CALCULATE(
    SUM(Autotask_ContractMilestones_Billed[amount]),
    FILTER(
        DISTINCT(Autotask_ContractMilestones_Billed[contractID]),
        Autotask_ContractMilestones_Billed[contractID] = Autotask_Projects[contractID]
    )
)

 

 

I will also have a look at the schema option which seems to be a good way forward too.

 

Thanks

 

T