topic Re: Variations affecting all rows in table in DAX Commands and Tips

Variations affecting all rows in table

MarkEden — Wed, 08 Jun 2022 09:59:16 GMT

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)

Re: Variations affecting all rows in table

v-yanjiang-msft — Mon, 13 Jun 2022 02:52:11 GMT

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.

Re: Variations affecting all rows in table

MarkEden — Mon, 13 Jun 2022 23:57:39 GMT

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.

Re: Variations affecting all rows in table

v-yanjiang-msft — Wed, 22 Jun 2022 07:27:19 GMT

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

Re: Variations affecting all rows in table

MarkEden — Wed, 22 Jun 2022 23:16:30 GMT

It is. Many thanks for your help.