How to use only one value per project name to calculate budget

[waterdatadude]

Hi All,

I'm trying to do something that I feel may be simpler than I'm making it out to be, but essentially I am trying to show budgets for various projects, however in the source data, the budget value is duplicated for multiple components of the project, so when they are summed for the visualization, it's much higher than it should be. I originally set out to remove all duplicates, but it's a big dataset with some general budgets that could be duplicated across multiple different projects, so if I were to remove all duplicates, it would potentially remove identical budgets for different projects.

In ArcGIS, I recall a simple button to ensure a visualization just pulled in one value per "category," but I can't seem to figure out a simple solution for this within Power BI. Any insight would be greatly appreciated! Happy to provide any more info to help shed light on this.

[jdbuchanan71 · Accepted solution]

@waterdatadude 

Give this a try.  It collapses eash Project / Budget into a single row then adds them up from there.

Project Budget Amount =
SUMX (
    GROUPBY ( 'Table', 'Table'[Project], 'Table'[Project Budget] ),
    'Table'[Project Budget]
)