Gauge min and max values help

[jam17]

I am working with a few different tables and am trying to show various pieces of information on a single dashboard: a mix of specific details about a customer, and some aggregate information about that customer's larger account (1 among many accounts).

Here is what I have so far, and what I am trying to accomplish:
Sales Table

customer_id	Year	sale amount
1	2023	$45
1	2024	$45
1	2024	$5
2	2024	$20
3	2024	$75
4	2024	-$10

Customer Table

customer_id	customer_name
1	John Doe
2	Kathy Sims
3	Bret Heart
4	Pat Blaine

Account Table

account_id	account_name
100	Curious Cat
200	Dainty Dog
300	Fancy Ferrets

Customer Account Join Table

customer_id	account_id
1	100
2	100
3	200
4	300

I have a slicer on my dashboard, along with a filter set to 2024, such that you could select John Doe and it will bring up a table of just his transactions from 2024, which will have a total row so you can see his total sales ($50).

With the following measure, I can also have a card populate the total transactions for his account (100) in 2024: $70
Account Total Sales = CALULATE(SUM(`SALES TABLE`[Sale Amount]),ALL(`Customer Account Join Table`,VALUES(`Customer Account Join Table`[account_id))

What I am trying to accomplish now is using a gauge to display how John Doe's account compare to the other accounts, but I can't figure out how to set properly set the Max and Mins.  In other words, when clicking on John Doe, the gauge will display Total Sales for his account $70 - with the gauge min being the lowest amount (-$10, Fancy Ferrets has only had a refund this year) and the highest amount ($75 for Dainty Dogs). In contrast, if I clicked on Kathy Sims, it would update the table to her transactions, but the guage would remain the same (since she belongs to the same account as John Doe), but would shift if a customer from a different account was chosen (keeping the min and max the same).

This way rather than using three different cards (one for Curious Cat - the account of the selected customer; one for the max; and one for the min), I can put all three pieces of this information in one visualization that will also display how close to the value is to the max/min (i.e. how much Curious Cat stacks up relative to other accounts, not some hard coded min/max).

The biggest problem I'm finding is because Account Total Sales is a measure, I can't put further aggregates on it, like min/max. 
What are my options?

[jam17 · Accepted solution]

Here is what ended up working for me: 
AccountTotalSales = 

CACLULATE(

SUM(`Sales Table`[sale amount]),

ALL(`Customer Account Join Table'),

VALUES(`Customer Account Join Table[account_id])

)

MaxAccountSales = 

MAXX(

SUMMARIZE(

ALL(`Customer Account Join Table`),

'Customer Account Join Table'[account_id],

"Max Sales",

CACLULATE(

SUM(`Sales Table`[sale amount]),

ALL(`Customer Account Join Table'),

VALUES(`Customer Account Join Table[account_id])

),

[Max Sales]

)

MinAccountSales = 

MINX(

SUMMARIZE(

ALL(`Customer Account Join Table`),

'Customer Account Join Table'[account_id],

"Min Sales",

CACLULATE(

SUM(`Sales Table`[sale amount]),

ALL(`Customer Account Join Table'),

VALUES(`Customer Account Join Table[account_id])

),

[Min Sales]

)

All of these are responsive to any filters I add to the page, so I don't need to make my code reference them.

[Anonymous]

Thanks for the reply from Sahir_Maharaj , please allow me to provide another insight: 
Hi  @jam17 ,

Here are the steps you can follow：

1. Create measure.

compare =
var _selectyear=SELECTEDVALUE('Sales Table'[Year])
var _selectcustomername=SELECTEDVALUE('Customer Table'[customer_name])
var _customerid=MAXX(FILTER(ALL('Customer Table'),'Customer Table'[customer_name]=_selectcustomername),[customer_id])
var _accountid=MAXX(FILTER(ALL('Customer Account Join Table'),'Customer Account Join Table'[customer_id]=_customerid),[account_id])
var _cusomeridcolumn=SELECTCOLUMNS(FILTER(ALL('Customer Account Join Table'),'Customer Account Join Table'[account_id]=_accountid),"test",'Customer Account Join Table'[customer_id])
return
SUMX(
    FILTER(ALL('Sales Table'),'Sales Table'[Year]=_selectyear&&'Sales Table'[customer_id] IN _cusomeridcolumn),[sale amount])

Min =
var _selectyear=SELECTEDVALUE('Sales Table'[Year])
var _table1=
ADDCOLUMNS(
    'Account Table',"Test",CONCATENATEX(FILTER(ALL('Customer Account Join Table'),[account_id]=EARLIER('Account Table'[account_id])),'Customer Account Join Table'[customer_id],"-"))
var _table2=
ADDCOLUMNS(
    _table1,"Value",SUMX(FILTER(ALL('Sales Table'),'Sales Table'[Year]=_selectyear&&CONTAINSSTRING([Test],'Sales Table'[customer_id])=TRUE()),[sale amount]))
return
MINX(_table2,[Value])

Max =
var _selectyear=SELECTEDVALUE('Sales Table'[Year])
var _table1=
ADDCOLUMNS(
    'Account Table',"Test",CONCATENATEX(FILTER(ALL('Customer Account Join Table'),[account_id]=EARLIER('Account Table'[account_id])),'Customer Account Join Table'[customer_id],"-"))
var _table2=
ADDCOLUMNS(
    _table1,"Value",SUMX(FILTER(ALL('Sales Table'),'Sales Table'[Year]=_selectyear&&CONTAINSSTRING([Test],'Sales Table'[customer_id])=TRUE()),[sale amount]))
return
MAXX(_table2,[Value])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Sahir_Maharaj]

Hello @jam17,

Can you please try this approach:

1. Create a Measure for Total Sales per Account

AccountTotalSales = 
CALCULATE(
    SUM('Sales Table'[sale amount]),
    ALL('Customer Account Join Table', 'Customer Table', 'Account Table'),
    'Customer Account Join Table'[account_id] = 
        SELECTEDVALUE('Customer Account Join Table'[account_id])
)

2. Create a Measure for the Minimum Sales across All Accounts

MinAccountSales = 
MINX(
    SUMMARIZE(
        ALL('Account Table'),
        'Account Table'[account_id],
        "TotalSales", 
        CALCULATE(
            SUM('Sales Table'[sale amount]),
            'Customer Account Join Table'[account_id] = 'Account Table'[account_id],
            'Sales Table'[Year] = 2024
        )
    ),
    [TotalSales]
)

3. Create a Measure for the Maximum Sales across All Accounts

MaxAccountSales = 
MAXX(
    SUMMARIZE(
        ALL('Account Table'),
        'Account Table'[account_id],
        "TotalSales", 
        CALCULATE(
            SUM('Sales Table'[sale amount]),
            'Customer Account Join Table'[account_id] = 'Account Table'[account_id],
            'Sales Table'[Year] = 2024
        )
    ),
    [TotalSales]
)

Hope this helps.

[jam17]

I am also getting an error with the following line:

'Customer Account Join Table'[account_id] = 'Account Table'[account_id],

It says "The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression".

[jam17]

By setting 

'Sales Table'[Year] = 2024

is that hard coding it?

How could I make the min/max be dynamic to the year selected by the filter?
Likewise, what if I wanted to add another filter, such as region?