cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
atavo Regular Visitor
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
atavo Regular Visitor
Regular Visitor

Re: Minimum value at different granularity

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

View solution in original post

7 REPLIES 7
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
Microsoft v-jiascu-msft
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.
atavo Regular Visitor
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

atavo Regular Visitor
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:

 

EXAMPLE.pngSample

Super User
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
https://www.linkedin.com/in/excelenthusiasts/
atavo Regular Visitor
Regular Visitor

Re: Minimum value at different granularity

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

View solution in original post

Super User
Super User

Re: Minimum value at different granularity

Hi @atavo,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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