Forum Discussion

MarkEden's avatar
MarkEden
Frequent Visitor
4 years ago
Solved

Variations affecting all rows in table

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 L...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

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.