Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
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/
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |