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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ChrisWeldon
New Member

Total for SumX - no context

First time poster asking for help, so if I have any problems with my post, please let me know.

 

I am trying to write a report to show the amount of premium for our contracts that is still in force, compared to the start of the year.

 

The data is held as follows:

 

tbl540

StatDt (Month End)GrAky (Contract Number)Premium
31/12/2018107047,699
31/01/2019107047,699
28/02/2019107047,699
31/03/2019107047,699
30/04/2019107047,699
31/05/2019107047,699
30/06/2019107049,053
31/07/2019107049,053
31/08/2019107041,178
30/09/2019107042,442
31/10/2019107044,501
30/11/2019107044,501
31/12/2019107044,501

 

The year for analysis is set by a simple table containing years, with a Slicer over it.  The measures [StartDate] and [EndDate] are then generated.  In this case they are 31/12/2018 and 31/12/2019.  

 

The InforcePremium at any time is calcuated using the measure:

       InforcePremium=sumx(tbl540,tbl540[Premium]*related(tblWrUm[USD Ex Rate]))

 

I need to cap the premium at the value at the start of the year, so if the premium goes above that, the start value applies.  I use the measure:

      InforcePremiumNoNew=sumx(values(tbl540[GrAky]),if([InforcePremium]>[Inforce Premium At Period Start (all periods)],[Inforce Premium At Period Start (all periods)],[InforcePremium]))

 

where [Inforce Premium At Period Start (all periods)]=calculate(calculate([InforcePremium],filter(tbl540,tbl540[StatDt]=[StartDate],all(tbl540[StatDt]))

 

This gives the following when put into a table:

StatDtInforcePremiumInforcePremiumNoNew
31/12/201847,699 
31/01/201947,69947,699
28/02/201947,69947,699
31/03/201947,69947,699
30/04/201947,69947,699
31/05/201947,69947,699
30/06/201949,05347,699
31/07/201949,05347,699
31/08/201941,17841,178
30/09/201942,44242,442
31/10/201944,50144,501
30/11/201944,50144,501
31/12/201944,50144,501
TOTAL601,42047,699

 

So the main table values calculate correctly.  The total row gives me some issues.  

 

The total for [InforcePremium] is never actually used as it is a point in time value, so can be ignored.

 

The total for [InforcePremiumNoNew] needs to show the final value in the period.  But obviously the StatDt context is not there.  I can use the HasOneValue to seperate the StatDt level values and the totals, but not sure what the formula for the no StatDt context should be.  If I apply a filter(tbl540,tbl540[StatDt]=[EndDate]) then the result is 0.

 

Any ideas what it should be?

 

Many thanks.  If anyone needs more info, happy to try and help.

 

Chris

 

2 REPLIES 2
sanalytics
Solution Specialist
Solution Specialist

@ChrisWeldon 

 

Please provide some dummy data with your power bi model and your exact output..It will help us to solve your problem fast.One advice, please make your content brief..

 

Regards,

Sanalytics

Hi Sanlytics, and thank you for your response.

 

Unfortunately our Corporate IT policy will not allow us access to any of the online storage facilities, or to email things like this to personal devices, hence the pasting of the relevant data into the tables.  

 

The solution I am looking for is how to get the mesaure InforcePremiumNoNew to show the last aviable value in the total row.

 

I hope someone can help.

 

Many thanks

 

Chris

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors