Minimum value at different granularity

[atavo]

Hi

I have a fact table with the following columns:

Date, CustomerID, BankAcctNo, Balance

The fact table basically captures the balances of customers' various deposit accounts daily.

PROBLEM:

I am tryng to find the minimum balance at the customer level (lower granularity).

At the moment, when using the MIN(Table(Balance)) measure, my result is at a the BankAcctNo level (higer granularity). I tried MINX(VALUES(CustomerID),SUM(Table(Balance)) with little success.

Grouping the data by Date and CustomerID and summing the balance in PowerQuery will solve my problem but I also need info at the BankAcctNo level for other analysis.

Appreciate any advice I can get

Alfred

[v-jiascu-msft]

Hi @atavo,

Could you please share a sample and the result you want? It seems you want to sum the balance first rather than find out the min record (a row).

Best Regards!

Dale

[atavo]

Correct

It should first sum the balances and then find the minimum balance. Please see sample below:

Sample

[Ashish_Mathur]

Hi @atavo,

You may refer to my solution here.

Hope this helps.

[atavo · Accepted solution]

Hi Ashish,

Thanks for your kind assistance,

this is exactly what I was after. For the benefit of other viewers, here is the measure/ solution:

MINX(

SUMMARIZE('calendar',

'calendar'[Date],

"ABCD",

SUM(Table1[Balance])),

[ABCD])

Alfred

Alfred

[Ashish_Mathur]

Hi @atavo,

If my reply helped, please give me kudos and mark my reply as Answer.

[Ashish_Mathur]

Hi,

Drag CustomeID column to the visual and use th efollowing measure

=CALCULATE(MIN(Data[Balance]),ALL(Data[BankAcctNo]))

Hope this helps.

[atavo]

Hi Ashish

Thanks for the assistance but this is not the solution I am after...the measure should first sum the deposit balances by customer by day i.e. (Data grouped by Date and customer and the respective balances are summed)...only then that you determine the minimum balance.

Alfred