The value from one query of quarterly data is adding to all quarters in another

[newbiePBIuser]

The Closed & Pipeline visuals are correct and come from two separate tables.

However, when I try to add the two fields together using SUMX it is adding the Q4 amount to all quarters and products.

Closed Sales w/Pipeline = SUMX('Closed','Closed'[Commissionable Revenue])+SUMX('PIPELINE','PIPELINE'[BG Rev Split])

The Pipeline in Q4 is adding to all of the quarters and not just Q4. For ex. there should be no changes in Q1-Q3

[Anonymous · Accepted solution]

Hi @newbiePBIuser ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Measure = 
VAR _qtr =
    SELECTEDVALUE ( 'Closed'[Quarter] )
VAR _fruit =
    SELECTEDVALUE ( 'Closed'[Fruit] )
VAR _closed =
    SUM ( 'Closed'[Commissionable Revenue] )
VAR _pipeline =
    CALCULATE (
        SUM ( 'PIPELINE'[BG Rev Split] ),
        FILTER ( 'PIPELINE', 'PIPELINE'[Quarter] = _qtr && 'PIPELINE'[Fruit] = _fruit )
    )
RETURN
    _closed + _pipeline

Closed Sales w/Pipeline = SUMX ( GROUPBY ( 'Closed', 'Closed'[Quarter], 'Closed'[Fruit] ), [Measure] )

Best Regards

[parry2k]

@newbiePBIuser not fully clear what you are trying to achieve. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490