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

Highlighted
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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 92 members 1,777 guests
Please welcome our newest community members: