How to Sum and accurately summarize when conditions are in place

[Nico_89]

Hello Everyone, 

I am trying to have a Sum calculation based on several columns per row, there is an identifier that indicates that 3 columns should be included in the sum, and for any others only 2 columns. 

The issue I am having is that the row level calculation is correct and works perfectly, but when grouping by customers the identifier gets ignored, and the total sum aggregates the 2-column formula when in reality there is an internal item that has the "True" identifier. 

The second row on the example above shows the calculation when the identifier is "True", and yet the total amount does not show the actual sum of the 2 rows that are part of the customer revenue total. 

Perhaps using summarize will solve the issue but I don't understand how the syntax will work on my case.

I have tried different measures and all end up the same, see them below

Revenue =

    IF(

        SELECTEDVALUE('Carriers'[FRS_Product]) = TRUE,

           CALCULATE(

                SUM('Master Accounts Bound'[Renewal Net House Commission])

                + SUM('Master Accounts Bound'[Agency Fees.amount])

                + SUM('Master Accounts Bound'[Policy Fee])

           )

        ,

            CALCULATE(

                SUM('Master Accounts Bound'[Renewal Net House Commission])

                + SUM('Master Accounts Bound'[Agency Fees.amount])

            )

    )

---------------------------------------------------------------------------------------------------------------------------

Revenue X =

    SUMX('Master Accounts Bound',

        IF(

        SELECTEDVALUE('Carriers'[FRS_Product]) = TRUE,

            'Master Accounts Bound'[Renewal Net House Commission]

            + 'Master Accounts Bound'[Agency Fees.amount]

            + 'Master Accounts Bound'[Policy Fee]

        ,

            'Master Accounts Bound'[Renewal Net House Commission]

            + 'Master Accounts Bound'[Agency Fees.amount]

        )

    )

-------------------------------------------------------------------------------------------------------------------

[tamerj1]

Hi @Nico_89 

please try

Revenue X =
SUMX (
CROSSJOIN ( 'Master Accounts Bound', VALUES ( 'Carriers'[FRS_Product] ) ),
IF (
'Carriers'[FRS_Product] = TRUE,
'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount] + 'Master Accounts Bound'[Policy Fee],
'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount]
)
)

[Nico_89]

Hi, 

Thanks for your reply but no, it's actually worse

[tamerj1]

@Nico_89 

What else do tou have in the table? Or in other words what are you slicing by in the table visual?

[Nico_89]

I am slicing by Customer, different products have different revenue calculations based on the FRS identifier, and in the case shown on the screenshot, the customer acquired 2 products, the revenue on one is calculated based on 2 columns and the other based on 3 columns. But the summary of the 2 products shows the result as if both products have False for the identifier and calculating revenue based on 2 columns for both. 

Data looks sort like this 

Rules for the Revenue are as follows

[tamerj1 · Accepted solution]

@Nico_89 

Not sure Customer from which table but could be something like 

Revenue X =
SUMX (
CROSSJOIN (
VALUES ( 'Master Accounts Bound'[Customer] ),
VALUES ( 'Carriers'[FRS_Product] )
),
VAR FRSProduct = 'Carriers'[FRS_Product]
RETURN
SUMX (
CALCULATETABLE ( 'Master Accounts Bound' ),
IF (
'Carriers'[FRS_Product] = TRUE,
'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount] + 'Master Accounts Bound'[Policy Fee],
'Master Accounts Bound'[Renewal Net House Commission] + 'Master Accounts Bound'[Agency Fees.amount]
)
)
)

[Nico_89]

This one worked pretty well, thank you very much!

I will need to review the formula and see how it works as I have no clue how it works, but heck it works! 😁