Forum Discussion
different result on different granularity
Hello, everyone,
For a given insurance policy with effective data 1/1/2021 and expire date 12/31/2021, the premium paid by insured is $1200. As illustrated in following measure [Unearned Premium], at 06/30/21, insurance company earns $600.00 given the policy canceled on 06/30/21, the remaining $600.00 should be returned to insured and booked as Unearned Premium.
The measure works on well on aggregate level such as State/Country but produces inaccurate result on policy granularity.
Appreciate your help.
Dennis
Unearned Premium =
VAR InforceDay = DATE ( 2021, 6, 30 )
RETURN
SUMX ( PolicyData,
PolicyData[Premium] * DIVIDE ( PolicyData[Expire Date] - InforceDay, PolicyData[Expire Date] - PolicyData[Effective Date] ) )
3 Replies
Hi 12Bowers12 ,
How did you set your visual? Could you please provide some screenshots of your visual as below screenshot? You can create another new measure as below to replace the orignal Values field and check if it can display the correct result under policy granularity...
Measure = SUMX ( VALUES ( PolicyData[Policy] ), [Unearned Premium] )And you can refer the methods in the following links to resolve your problem.
Use IsInScope to get the right hierarchy level in DAX
Working below a DAX formula’s granularity
Wrong Total in Matrix with Parent and Child
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907You might also use MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...
Best Regards
Thanks lot, I will give it try and let you know whether it works out.
Hi 12Bowers12 ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
Best Regards
