Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BHAVIKCHAWLA
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.

 

RegionManager_NameAgent_Name
EastKevinApple
EastKevin

Mango

CentralLouisBanana
CentralLouisOrange
EastMarkPineapple
EastLucasWatermelon
IDAgentSales
1Apple60
2Mango40
3Banana30
4Orange70
5Pineapple50
6Watermelon50

 

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
Someone please help me in DAX
Please not percentage will be displayed in card.

1 ACCEPTED SOLUTION
Alex87
Solution Supplier
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

 

 if this solution answers your needs please mark it as a solution

 

Best regards,

Alex

View solution in original post

2 REPLIES 2
Alex87
Solution Supplier
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

 

 if this solution answers your needs please mark it as a solution

 

Best regards,

Alex

Thanks Alex!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.