cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mike_0828
Frequent Visitor

Measure iterating subset of data

Hi guys!

I have a Fact table with accounts receivable (AR) and accounts payable (AP) amounts for each counterpaty.

The goal is to create measure with settlement amount equals MIN of AR and AP.

My current measure (Settlement_amt) works fine if I specify counterparty code as context, but if my context is Region or Category it works incorrectly.

I would like to create a measure which iterates over Dim_Cust_Vend and calculates Settlement_amt individually.

 

Solutions are very appreciated!!

2019-07-17_17-47-18.png

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

-- Replace your old Settlement_amt with the following:

[Settlement Amount] =
var __settlementAmount =
	SUMX(
		VALUES( Dim_Cust_Vend[Code] );
		MIN( [AR]; [AP] )
	)
return
	__settlementAmount

-- This will work in any scenario and is an extension of the old measure.

Best

D.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable


@Mike_0828 wrote:

Hi guys!

I have a Fact table with accounts receivable (AR) and accounts payable (AP) amounts for each counterpaty.

The goal is to create measure with settlement amount equals MIN of AR and AP.

My current measure (Settlement_amt) works fine if I specify counterparty code as context, but if my context is Region or Category it works incorrectly.

I would like to create a measure which iterates over Dim_Cust_Vend and calculates Settlement_amt individually.

 

Solutions are very appreciated!!

2019-07-17_17-47-18.png


Mate, you say:

 

I would like to create a measure which iterates over Dim_Cust_Vend and calculates Settlement_amt individually.

 

Well, if it calculates Settlement_amt individually, then it must do something with the amounts because a measure can only return a number or text. So then, what do you want to do with the amounts? Take an average? Sum them up? Take the min or max? What's the aggregator for the amounts?

 

Best

Darek

Darek,

I'd like to sum up settlement amount when Region or Category is specified as a filter for Dim_Cust_Vend.

Anonymous
Not applicable

-- Replace your old Settlement_amt with the following:

[Settlement Amount] =
var __settlementAmount =
	SUMX(
		VALUES( Dim_Cust_Vend[Code] );
		MIN( [AR]; [AP] )
	)
return
	__settlementAmount

-- This will work in any scenario and is an extension of the old measure.

Best

D.

View solution in original post

This brief formula really works!

Could you explain the meaning of VALUES()?

I tried to remove VALUES from formula and system showed me an error:

A single value for column 'Code' in table 'Dim_Cust_Vend' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Anonymous
Not applicable

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors