Dynamic field based on subset

[reggiebob]

Hello,

I am hoping someone can help me with what I am trying to accomplish.

Please see my example below. I would like to display a column that indicates if the Contract is single site or multi site based on the % value of the ShiptoCityState and date selected. If one ShiptoCityState within a contract has 75% or more of the total sales for the contract that the whole contract would be listed as a single site. If there are no ShiptoCityState's within the contract that hold 75%+ of the sales for that contract the whole contract is Multi Site contract. If the customer is not linked to a contract at all it would be listed as No Contract. If I move the % Value slicer to 60% I would like the SiteSize to update based on the % selected. I would also like to have a slicer to select each of the metrics as well. Any ideas? Thank you in advance for your help!

Example:

I am not able to upload attachments so I've provided the data tables below.

Customer table: 

Customer	Contract	ShiptoCityState
1		ChicagoIllinois
2	1564	LincolnNebraska
3	1946	PhoenixArizona
4	1564	LincolnNebraska
5	1564	BismarkNorthDakota
6	1946	LasVegasNevada
7	1844	DallasTexas
8	1844	FortWorthTexas
9	1844	FortWorthTexas
10	1564	AmesIowa

Sales Table:

Customer	Month	Sales
1	12/1/2024	65123
2	12/1/2024	31912
3	12/1/2024	987416
4	12/1/2024	1984
5	12/1/2024	15189
6	12/1/2024	1968546
7	12/1/2024	11654
8	12/1/2024	984651
9	12/1/2024	49843
10	12/1/2024	4864
1	1/1/2025	516854
2	1/1/2025	15665
3	1/1/2025	16581
4	1/1/2025	87241
5	1/1/2025	8946
6	1/1/2025	76532
7	1/1/2025	64352
8	1/1/2025	43516
9	1/1/2025	31331
10	1/1/2025	465146

% Table: 

% Value
0.00%
5.00%
10.00%
15.00%
20.00%
25.00%
30.00%
35.00%
40.00%
45.00%
50.00%
55.00%
60.00%
65.00%
70.00%
75.00%
80.00%
85.00%
90.00%
95.00%
100.00%

SiteSize Table:

SiteSize

Single Site

Multi Site

No Contract

[MFelix · Accepted solution]

Hi @reggiebob ,

Add the following measures to your model:

Location % = 
            DIVIDE(
			SUM(Sales[Sales]),
			CALCULATE(
				SUM(Sales[Sales]),
				REMOVEFILTERS(Customer[ShiptoCityState])
			)
		)



Site size = 
        SWITCH(
			TRUE(),
			SELECTEDVALUE(Customer[Contract]) = BLANK(), "No Contract",
			MAXX(
				ALLSELECTED(Customer[ShiptoCityState]),
				[Location %]
			) >= MIN('% Table'[% Value]), "Single Site",
			"Multi Site"
		)

Filter by site size = IF([Site size] in VALUES('Site size'[SiteSize]), 1)

Use the measures on your visuals and the Filter by size should be placed on the filter pane for the specific visual with the option is not blank.

Please see attach file.

[reggiebob]

This worked perfectly! Thank you so much for your help. 😁