Question on a Measure that's Failing

[BillWalker]

Greetings community!

I'm struggling with a Measure I am using in a report that looks at a table of sales opportunities. The idea is to only sum up the total of  'Billing Amount' if an opportunity name is unique. We have lots of opportunities duplicated in this table as multiple partners can be associated with any given opportunity. So the idea is to look for only the unique entries in the 'Opportunity Name' column and then sum up the 'Billing Amount' column for those resulting rows.

To make it more confounding, this worked with my data set yesterday and last week perfectly. Today, I refreshed the underlying data set and now I'm getting the "A table of multiple values was supplied where a single value was expected" message.

The data set looks like this:

FY23 Opps

Account Name	Partner Name	Opportunity Name	Billing Amount
ACME Corp	Partner 1	Opp 1	1000
ACME Corp	Partner 2	Opp 1	1000
Badda Bing	Partner 3	Opp 2	500
Contoso	Partner 4	Opp 3	750
Tailspin Toys	Partner 3	Opp 4	850

The DAX expression I have supplied (and that worked yesterday and last week) is:

Measure for Unique Billing Amount Sum = SUMX(DISTINCT('FY23 Opps'[Opportunity Name]),CALCULATE(DISTINCT('FY23 Opps'[Billing Amount])))

If I revert my data set back to last week's data, no issues with this measure. I have visually compared last week's data with today's data and I don't see any differences aside from a a few more new opportunities in the data set. Any thoughts on if my measure above is the best/right way to tackle this?

[BillWalker]

Thank you Janey! This did resolve the initial issue, I greatly appreciate your help!

In this report, I am using "Sync Sliders" to have a first page that is all filters and subsequent pages are the actual reports. It now looks like these new measures are not respecting the filters from the first page of the report (the filters page). 

Can you elaborate on the measure you wrote above? SUMMARIZE creates a new table right? I assume that's impacting the new measure's ability to respect the global 'sync sliders'.

[v-janeyg-msft · Accepted solution]

Hi, @BillWalker 

Try to change all to allselected, so the result will change with the context.

measure =
SUMX (
    SUMMARIZE (
        ALLselected ( 'Table' ),
        [Opportunity Name],
        "a", MAX ( 'Table'[Billing Amount] )
    ),
    [a]
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

Hi, @BillWalker 

"A table of multiple values was supplied where a single value was expected" message." This error message should be from dax code. But you only provided one measure, and there was no error according to the sample you provided,

If it's wrong, it must be in conflict with your context. To avoid this problem, you can use summarize() to create a context yourself.

Like this:

measure =
SUMX (
    SUMMARIZE (
        ALL ( 'Table' ),
        [Opportunity Name],
        "a", MAX ( 'Table'[Billing Amount] )
    ),
    [a]
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey