Forum Discussion
Minimum value at different granularity
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
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
7 Replies
Hi,
Drag CustomeID column to the visual and use th efollowing measure
=CALCULATE(MIN(Data[Balance]),ALL(Data[BankAcctNo]))
Hope this helps.
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
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
Correct
It should first sum the balances and then find the minimum balance. Please see sample below:
Sample
