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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors