cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Card visualization showing percent of total

Hi All, I want to show manager's team(agents) performance compared to other team of same region.
Only card and one slicer is present in report. Slicer is of manager and card shows performance of its team as percentage.
Below is the sample data. Two tables are there.

 Region Manager_Name Agent_Name East Kevin Apple East Kevin Mango Central Louis Banana Central Louis Orange East Mark Pineapple East Lucas Watermelon
 ID Agent Sales 1 Apple 60 2 Mango 40 3 Banana 30 4 Orange 70 5 Pineapple 50 6 Watermelon 50

For example if manager "Kevin" is selected in slicer, then card should show value = (60+40)/(60+40+50+50) = 50%
---Kevin's team/Total sales within Kevin's region
Please not percentage will be displayed in card.

1 ACCEPTED SOLUTION
Solution Supplier
Solution =

VAR __ValuesManager = VALUES(DIM[Agent_Name])
VAR __ValuesRegion = VALUES(DIM[Region])

VAR __Nomin = CALCULATE(SUM(Data[Sales]), Data[Agent] IN __ValuesManager)

VAR __DenomValues =
CALCULATETABLE(
VALUES(DIM[Agent_Name]),
REMOVEFILTERS(DIM),
DIM[Region]  IN __ValuesRegion
)
VAR __DeNomin = CALCULATE(SUM(Data[Sales]), REMOVEFILTERS(DIM[Manager_Name]), Data[Agent] IN __DenomValues)

VAR __Result = DIVIDE(__Nomin, __DeNomin)

RETURN
__Result

Please consider the Manager_Name slicer coming from the DIM table (Agent_Name, Manager_Name, Region) and

in terms of model : one to many relationship on Agent_Name

Best regards,

Alex

2 REPLIES 2
Solution Supplier
Solution =

VAR __ValuesManager = VALUES(DIM[Agent_Name])
VAR __ValuesRegion = VALUES(DIM[Region])

VAR __Nomin = CALCULATE(SUM(Data[Sales]), Data[Agent] IN __ValuesManager)

VAR __DenomValues =
CALCULATETABLE(
VALUES(DIM[Agent_Name]),
REMOVEFILTERS(DIM),
DIM[Region]  IN __ValuesRegion
)
VAR __DeNomin = CALCULATE(SUM(Data[Sales]), REMOVEFILTERS(DIM[Manager_Name]), Data[Agent] IN __DenomValues)

VAR __Result = DIVIDE(__Nomin, __DeNomin)

RETURN
__Result

Please consider the Manager_Name slicer coming from the DIM table (Agent_Name, Manager_Name, Region) and

in terms of model : one to many relationship on Agent_Name

Best regards,

Alex

New Member

Thanks Alex!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors