cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

A single value cannot be determined for a measure

Hello,

 

I'm trying to create a measure, but am getting the "single value cannot be determined" error.  I successfully created this a calculated column, but hope to use a measure instead.

 

I'm trying to break out amounts by transaction type.  In this example, I want a measure that returns only "forecast" transaction amounts...

 

Forecast =

IF ('SALES_TABLE'[TRANSACTION_TYPE] = "FORECAST",

'SALES_TABLE'[AMOUNT],

0)

 

Breaking out amounts by transaction type would allow me to populate a Power BI table as follows...

 

Measure.PNG

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: A single value cannot be determined for a measure

Why wouldn't you just put the transaction type as a column in a matrix?

Highlighted
Helper II
Helper II

Re: A single value cannot be determined for a measure

My example is a simplistic version of what I'm actually needing it for.  Just wanted to lay out the goal of a measure in a very basic fashion.  The reasons I would like to be able to have "transaction type" measures include...

 

(1) To use the Actual and Forecast numbers to then calculate Variance and Variance % in a table/matrix format...

Capture.PNG

 

 

(2) To have the ability to create synthetic transaction types without grouping (eg, using Open Orders twice in the same chart)...

 

Capture2.PNG

Highlighted
Community Support
Community Support

Re: A single value cannot be determined for a measure

hi, @Trudgeon

If you just want to transform your column to measure you could try this formula

Measure = CALCULATE(SUM(SALES_TABLE[Amount]),FILTER(SALES_TABLE,SALES_TABLE[TRANSACTION_TYPE]="FORECAST"))+0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User IV
Super User IV

Re: A single value cannot be determined for a measure

Hi,

 

Drag Customer to the Row labels and Transaction type to the column labels.  Write this measure

 

=CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[Transaction Type]="Forecast"))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors