Sum amounts related to specific value in column

[kennoe]

I want to sum amounts based on a value that appears in a separate column. The formula I have tried is:

"Revenue = CALCULATE(SUM('Initiative Funding'[Amount]),ALLEXCEPT('Initiative Funding','Initiative Funding'[Funding Status]="Expected Revenue"))". 

I am getting this error message:

"A single value for column 'Funding Status' in table 'Initiative Funding' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I don't understand this error, as I am specifying the value required in the Funding Status column.

Steve

[v-yulgu-msft]

Hi @kennoe,

Please try this:

Revenue =
CALCULATE (
    SUM ( 'IF_Example41'[Amount] ),
    FILTER (
        ALLEXCEPT ( 'IF_Example41', 'IF_Example41'[Funding Status] ),
        SELECTEDVALUE ( IF_Example41[Funding Status] ) = "Expected Revenue"
    )
)

Best regards,

Yuliana Gu

[kennoe]

Thank you. The amended formula removes the error message, however it does not show any values in the table. Things I noticed when trying to get a solution:

1.  I used the wrong column name in my formula ("Funding Status" should have been "Funding Class") but when I corrected this I still did not get any values for the Revenue measure.
2. The table has a filter on Funding Status to show only "Released" status. Could this prevent the measure from working, as the Funding Class value of "Expected Revenue" has a Funding Status of "Planned", not "Released"? 

Steve

[PattemManohar]

Could you please post sample input data and excepted output...

[kennoe]

A sample file can be found here:

https://www.dropbox.com/s/ah71fhvm606jl92/Example42.pbix?dl=0