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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Taniya
Frequent Visitor

Need help with a dax query

 

Hi
So below is the screenshot of Rref(Risk reference ),qref(quote reference),start date,end date and annual gross premium(which we need to calculate). What we have achieved correctly is annual gross premium column but what not is the summation it is providing. As you see Rref is same in all cases so for a particular Rref and its dintinct Qref we want only annual gross premium whose start date is latest among the two same qref for a risk in that club year . 
Ex for 2017 annula gross premium  should be 10000,for 2018 it will be 10000 for 2019 = 9986.33 ,for 2020 =9986.33,for 2021 = 9986.33. sos alltogether it should be 49958.99 but what we are getting as total is 29.986.33.
DAX for what we have achieved so far is also attached below -

 

Annual Gross perm = IF([Distinct Count]=2,

SUMX(

FILTER(

GROUPBY( 'Club Year Data','Club Year Data'[Rref],'Club Year Data'[Qref],'Club Year Data'[spl_stagingid], 'Club Year Data'[spl_perdaygrossperm],'Club Year Data'[productname],'Club Year Data'[spl_startdate],'Club Year Data'[Trim name]),

 

SELECTEDVALUE('Club Year Data'[Club Year])=YEAR('Club Year Data'[spl_startdate])),

'Club Year Data'[spl_perdaygrossperm]*365),

SUMX( GROUPBY( 'Club Year Data','Club Year Data'[Rref],'Club Year Data'[Qref],'Club Year Data'[spl_stagingid], 'Club Year Data'[spl_perdaygrossperm],'Club Year Data'[productname],'Club Year Data'[Trim name]),

'Club Year Data'[spl_perdaygrossperm]*365) )
Please Note - Distint Count=DISTINCTCOUNT(Club Year Data[qref]) and Trim name is the name of broker
Please help me with this.

 

Capture 1.PNG

2 REPLIES 2
harshnathani
Community Champion
Community Champion

Hi @Taniya ,

 

To perform any aggregrate function using group by, you need to use CURRENTGROUP(). I do not see you using this anywhere in you formula.

 

Please check this article 

https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

 
Also, please have a look at the below video.
 
It is very diffcult to follow you question. Please share some sample data to help you further on this.
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 

is q1 and q2 are two quotes attaches to the same risk and if end date of q1 =start date of q2 then only consider that date for q2 i.e the latest quote

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.