Minimum value for sum - bank scenariio

[atavo]

Hi appreciate the assistance with reference to the Data Table below:

Is there a measure that captures a customer's minimum account balance:

1. at the lowest granularity (i.e. AccountNo level) but

2. at the customer level, it captures the minimum value of the sum of account balances for respective customers

Have included what the desired result should be.

At the moment, I am using powerpivot to extract the minimum balance at the deposit account level.

I then sum the minimum balance of a customers various deposit accounts to arrive at a minimum balance at the customer level using sumif. With data going to the millions of rows, this is just not a practical solution.

Thanks

Alfred

[Eric_Zhang]

---

@atavo wrote:

Hi appreciate the assistance with reference to the Data Table below:

Is there a measure that captures a customer's minimum account balance:

1. at the lowest granularity (i.e. AccountNo level) but

2. at the customer level, it captures the minimum value of the sum of account balances for respective customers

Have included what the desired result should be.

At the moment, I am using powerpivot to extract the minimum balance at the deposit account level.

I then sum the minimum balance of a customers various deposit accounts to arrive at a minimum balance at the customer level using sumif. With data going to the millions of rows, this is just not a practical solution.

Thanks

Alfred

---

@atavo

Is 300 for the customer Jack in Aug-17 a typo? My understanding is that the value shall be 250(50+200)? If my understanding is correct, you can create a measure as below. See more details in the attached pbix file.

Measure =
VAR summizedTbl =
    SUMMARIZE (
        'yourTable',
        yourTable[Customer],
        yourTable[AccountNo],
        yourTable[YearMon],
        "minBal", MIN ( yourTable[Balance] )
    )
RETURN
    SWITCH (
        TRUE (),
        ISFILTERED ( 'yourTable'[Customer] ) && ISFILTERED ( yourTable[AccountNo] ), MIN ( yourTable[Balance] ),
        ISFILTERED ( 'yourTable'[Customer] ) && ISFILTERED ( yourTable[YearMon] ), SUMX ( summizedTbl, [minBal] ),
        ISFILTERED ( 'yourTable'[Customer] )
            && NOT ( ISFILTERED ( yourTable[YearMon] ) ), CALCULATE (
            MIN ( 'Table'[sumMinBal] ),
            ALLEXCEPT ( yourTable, yourTable[Customer] )
        ),
        MIN ( yourTable[Balance] )
    )

[atavo]

Hi Eric
It's not a typo. At the customer level, the total balance for Jack on 25/8 and 26/8 are 300 and 350 respectively. Hence, minimum value is 300

[vanessafvg]

@atavo sorry just to clarify do you mean minimum balance at the earliest date?

if that is what you mean using something like first date might work ie

measure =
CALCULATE ( SUM ( data[balance] ), FIRSTDATE ( data[date] ) )

[atavo]

Thanks Vanessa
I am after a measure that:
1. Determines the minimum balance for each respective month and
2. If I slice the measure in 1. by AccountNo, it'll give me the minimum balance of each respective AccountNo for each respective month
3. Alternatively, if I slice the measure in 1. by Customer, it'll give me the minimum value of the sum of account balances for all accounts of each respective customer for each respective month