cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

Re: Minimum value at different granularity

Hi Ashish,

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 7
Super User

Re: Minimum value at different granularity

Hi,

Drag CustomeID column to the visual and use th efollowing measure

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft

Re: Minimum value at different granularity

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Re: Minimum value at different granularity

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

Regular Visitor

Re: Minimum value at different granularity

Correct

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

Sample

Super User

Re: Minimum value at different granularity

Hi @atavo,

You may refer to my solution here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

Re: Minimum value at different granularity

Hi Ashish,

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

Super User

Re: Minimum value at different granularity

Hi @atavo,

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,331)