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 |
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.
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
