cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSWADOOD
Helper I
Helper I

SUM BASED ON RANKX

Hello,

i am unable to get sum using measures based on rankx based on my two filters Top client premium wise and top client claim ratio wise.

below is a screenshot of my dashboard

SSWADOOD_2-1628843399534.png

i am trying to get sum of commssion expense with the following dax

MEASURE3 = 
IF (
    [A2]<=SELECTEDVALUE(CLAIMRATIORANK_SLAB[CLAIMRATIORANK]),CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION]),
FILTER (ALL(DETAILS),[A2]<=SELECTEDVALUE(CLAIMRATIORANK_SLAB[CLAIMRATIORANK]))),
    AGENT_COMMISSION[AGENTCOMMBETWEENDATES]
)

 

where A2 is the ranking based on the based on the claim ratio column (which is a measure as well). 

 

i have tried to do the same thing for my top client premium wise filters and it works great by some how it doesnt work on this claim ratio filter

MEASURE2 = 
IF (
    [A1]<=SELECTEDVALUE(GROSSPREMRANK_SLAB[GROSSPREMRANK]),CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION]),
FILTER (ALL('DETAILS'),[A1]<=SELECTEDVALUE(GROSSPREMRANK_SLAB[GROSSPREMRANK]))),
    AGENT_COMMISSION[AGENTCOMMBETWEENDATES]
)

this one works fine as its supposed to shown below

SSWADOOD_3-1628843920401.png

 

any tips on what can i do to fix  DAX measure 3 and get it to work on  top 20 clients claim ratio wise filter

 

Thanks and regards 

shaharyar

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@SSWADOOD , If you can get number value from same tbale you are using TOP 20 , 30, Then try to use TOP like

 

CALCULATE( CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION])),TOPN( selectedvalue(TOPN[TOPN]),allselected(Date[Date]),CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION])),DESC), values(GROSSPREMRANK_SLAB[GROSSPREMRANK]))

 

 

refer for how it works

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@SSWADOOD , If you can get number value from same tbale you are using TOP 20 , 30, Then try to use TOP like

 

CALCULATE( CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION])),TOPN( selectedvalue(TOPN[TOPN]),allselected(Date[Date]),CALCULATE(SUM(AGENT_COMMISSION[AGENT_COMMISSION])),DESC), values(GROSSPREMRANK_SLAB[GROSSPREMRANK]))

 

 

refer for how it works

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

thanks @amitchandak for the reply, the video really helped alot. got it to work

calculate([agentcommbetweendates],
topN(SELECTEDVALUE(CLAIMRATIORANK_SLAB[CLAIMRATIORANK]),allselected(details[policy_num]),[CLAIM_RATIO],desc)
,values(details[policy_num]))

thanks 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors