Slicers and Cluster Column Charts

[basicfinance14]

Hi,

I'm completing the below tasks (pictures provided with background information) and are stuck on creating slicers, the clustered column chart and the link between them - primarily through the DAX measure(s). I've been able to create the below chart and slicers, however my formula does not seem accurate. My current formula's are:

New Insurance Result PFI =

(2200000*(PFI_PGR[PFI_PGR Value]/100))-(2200000*(PFI_CLR[PFI_CLR Value]/100))

New Insurance Result Senior Dogs =

(2000000*(SD_PGR[SD_PGR Value]/100))-(2000000*(SD_CLR[SD_CLR Value]/100))

Can anyone please assist? 

Thank you!!

Task Description 1Task Description 2Task Description 3

Slices and Charts

[basicfinance14]

Thank you so much, that's a massive help. I really appreciate it!

[TobyNye · Accepted solution]

I made a sample dataset with 2 tables:

Basically just the data that you shared in your original post and then some years to forecast against. I constructed a few quick visuals to try and come up with the most sensible options. In my opinion the first point would be to determine which strategy we want to move forward with (Autonomous Growth or Senior Dogs). So I have created a line graph mapping the IR value over the forecast dates; the legend is the PGR and respective CLR values; it is then split into each strategy on the same axis:

This makes the choice for us; Senior Dogs have much better initials and forecasts so it's a no-brainer there. Now that we know we want Senior Dogs, I then mapped visuals for the cumulative revenue and flat IR value for each year on ribbon charts. Ribbon charts make it very easy to see the movement between ranks of a particular group (which is exactly what we want here):

This makes it easy to see which PGR values will return the highest in year and cumulative profit respectively. From there it would be a matter of opinion and need, based on what your timelines look like and what is wanted. The highest PGR band gets to the top of the in year (IR) revenue graph in 2028 but still hasn't reached the top of the cumulative revenue graph even in 2030! You could make a case for a number of these different PGR values and do a deeper dive into the top 3 considerations or so. I'm putting measures and explanations below:

Base Premium Income =

/*This returns the number of years on or before this one in the forecast*/

VAR _rows = COUNTROWS(FILTER(ALL(Years), Years[Year] <= MAX(Years[Year])))

RETURN

/*This takes the initial revenue amount and multiplies it by the selected PGR value, it is raised to the power of the number of years - 1 as they include themselves, thus 2023 will be  1 - 1 = 0 and just the base revenue amount so makes sense*/

SELECTEDVALUE(Data[BASE PREMIUM INCOME]) * (( 1 + SELECTEDVALUE(Data[PGR DECIMAL]))^(_rows - 1))

_________________________________________________________________________________________________________________________

/*Taking the previous calculation and multiplying through by the CLR figure to get the value lost in claims in the given forecast year*/

Claims Lost Val = [Base Premium Income] * SELECTEDVALUE(Data[CLR DECIMAL])

_________________________________________________________________________________________________________________________

/*IR just being your net profit is the income - losses*/

IR = [Base Premium Income] - [Claims Lost Val]

_________________________________________________________________________________________________________________________

/*This is calculating a running total for IR, so if the year is 2023 it is just the IR for 2023; if it's 2025 it's the sum of profit in 2023, 2024, 2025. If you want more of an explanation on how this works let me know.*/

Total Revenue = SUMX(FILTER(ALL(Years),

                        Years[Year] <= MAX(Years[Year])

),

CALCULATE([IR])

)

I have a few other measures in my example set but I'm not using them, they were just ideas. Hope this helps, let me know if you have any further questions/issues.

[TobyNye]

I think you are slightly misunderstanding what their growth rate represents here, I also don't think the visuals that the question is asking for a very useful considering what they are trying to discern. The growth rate is just saying how much more your 'base premium income' would be the following year; whereas the 'claims loss ratio' is how much of the 'base premium income' was lost to claims. Your 'IR' or yearly net profit would just be 'base premium income' * (1 - 'claims loss ratio). The growth rates are just indicating that next year your 'base premium income' would be 'base premium income LAST YEAR' * (1+ 'premium growth rate'). The visuals they have asked you to create don't care about the date at all and thus the 'PGR' is neglected and not used. It would make much more sense to me if you were to map out the initial profits from both from the formula's I put above; but then to extrapolate that onto future years and create a forecast. Identify the 'takeover' points where an option that has a higher 'CLR' and 'PGR' begins to generate more profit than the others. Also identify the date where they have a higher cumulative total revenue (the point at which they have actually generated more total revenue than other options). This would allow the hypothetical company to decide which option to take based on immediate and forecast profit; it would also give them the ability to choose to play for the short or long term. This shouldn't be very hard to set up but let me know if you need help, I'll have a go at making something after I've built a sample dataset.

[basicfinance14]

Thank you so much for your response! I have absolutely no experience using power bi and are not confident with numbers, so this is completely outside my comfort zone. 

I've updated the new IR formula based on your suggestion - thank you again. Can you please assist with the calculation of them premium income and claim amount DAX formulas following on from this? My initial calculations have been:

SD_NewClaim = CALCULATE(2000000*(SD_CLR[SD_CLR Value]/100))

SD_NewPremInc = CALCULATE(2000000*(1+SD_PGR[SD_PGR Value]/100))

Apologies, I can't figure out how to share my power bi file to make things easier.

Here's my updated chart. Thank you for the suggestions. I've generated a forecast separately based on actual premium income data in my power bi file.