cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trudgeon Regular Visitor
Regular Visitor

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
jthomson New Contributor
New Contributor

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 Regular Visitor
Regular Visitor

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 Team
Community Support Team

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
Super User

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 131 members 1,977 guests
Please welcome our newest community members: