Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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:
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
Hi @atavo,
If my reply helped, please give me kudos and mark my reply as Answer.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |