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
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.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors