Dear All,
I'm in dire need of DAX help in PowerBI, please.
I have a history of policy transactions. I need to take Running Total Premium for all transactions and divide it by Limit, but only for the latest transaction for each policy.
I'm attaching an Excel file with my mock-up data and measures and adding a screenshot (if it works for me). I'm pretty sure the issue is with my Running Total premium and its filters. The Running Total itself works in Power BI, just not for my purpose. (Incidentally, it throws an error in Excel's Power Pivot and won't work at all).
Thank you,
Blanka
See if the attached solution tackles your problem. Not sure I fully understand your case though.
I added a calculated column that functions like a SUMIF on each row based on the Policy. The running total measure sums that column but only for the rows where the 'latest transaction' = Yes.
You could add a couple of calculated columns:
LatestLimit: =if(MyTable[LatestTransaction]="Yes",MyTable[Limit],BLANK())
LatestLimitBand: =LOOKUPVALUE([LimitBand],MyTable[LatestTransaction],"Yes",MyTable[Policy],MyTable[Policy])
then use LatestLimitBand as the row field, LatestLimit to get the total limit, and just sum the original AnnualizedPremium.
So, the secret lies in calculated column(s) and not in a measure. I did not even think about it and was not familiar with some of these formulas.
I can work with any of these solutions now. I will see how they affect the size of my file once I incorporate them and will pick one.
Thank you both, Riny and Velouria! I wanted to mark both your answers as "Answers Post", but it will not let me do that. I appreciate you both.
Glad you worked it out either way!
