Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I'm new to PowerBI & DAX and am struggling with creating a variation on a comparison of insurance products premiums for last 12 months agains prior 12 months. I've managed to create the LTM and PTM measures and this works for the insurer selected in a page slicer. The issue I have is that when certain insurers are selected I need to add back premiums for some products from a different insurer. Something like:
If insurer A is selected then show LTM and PTM allocated to A
If Insurer B is selected then show LTM and PTM allocated to B plus LTM and PTM for A but only for products 1 and 2
I'm trying to do this with measures to calculate the individual amounts and then with a SWITCH( TRUE(), measure. This works for the total, but it also adds to each row throwing those figures out. I'd appreciate if anyone can help me find a way to add only to the relevent product rows.
Here are my SWITCH( TRUE() formula and the table
Total including variations =
SWITCH( TRUE(),
[Selected Insurer] = "Insurer A", [Total Premium] + [Insurer C Accident & Health Premium] + [Insurer C Professional Risks Premium],
[Selected Insurer] = "Insurer B", [Total Premium] + [Insurer C Marine Premium],
[Total Premium)
Solved! Go to Solution.
Hi @MarkEden ,
According to your description, in my understanding, you want for example for Insurer B, only when the Risk Class row is Marine, it should be "[Total Premium] + [Insurer C Marine Premium]", otherwise, it should be "[Total Premium]".
If this is the case, here's my solution.
Total including variations =
SWITCH (
TRUE (),
[Selected Insurer] = "Insurer A",
[Total Premium] + [Insurer C Accident & Health Premium] + [Insurer C Professional Risks Premium],
[Selected Insurer] = "Insurer B",
IF (
MAX ( [Risk Class] ) = "Marine",
[Total Premium] + [Insurer C Marine Premium],
[Total Premium]
),
[Total Premium]
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MarkEden ,
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team _ kalyj
It is. Many thanks for your help.
@v-yanjiang-msft thanks for this. I'll test this out. I think it might need a couple more variations but I can see the logic now.
Hi @MarkEden ,
According to your description, in my understanding, you want for example for Insurer B, only when the Risk Class row is Marine, it should be "[Total Premium] + [Insurer C Marine Premium]", otherwise, it should be "[Total Premium]".
If this is the case, here's my solution.
Total including variations =
SWITCH (
TRUE (),
[Selected Insurer] = "Insurer A",
[Total Premium] + [Insurer C Accident & Health Premium] + [Insurer C Professional Risks Premium],
[Selected Insurer] = "Insurer B",
IF (
MAX ( [Risk Class] ) = "Marine",
[Total Premium] + [Insurer C Marine Premium],
[Total Premium]
),
[Total Premium]
)
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |