cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trudgeon 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
jthomson 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?

Trudgeon 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

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.
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors