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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 Supplier
Solution Supplier

@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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors