Forum Discussion

Mike_0828's avatar
Mike_0828
Frequent Visitor
7 years ago
Solved

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

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    7 years ago
    -- 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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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!!

    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

    • Mike_0828's avatar
      Mike_0828
      Frequent Visitor
      7 years ago

      Darek,

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        -- 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.