topic Re: How to Sum and accurately summarize when conditions are in place in DAX Commands and Tips

How to Sum and accurately summarize when conditions are in place

Nico_89 — Fri, 07 Jul 2023 15:49:52 GMT

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]

)

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

Re: How to Sum and accurately summarize when conditions are in place

tamerj1 — Fri, 07 Jul 2023 16:35:55 GMT

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]
)
)

Re: How to Sum and accurately summarize when conditions are in place

Nico_89 — Fri, 07 Jul 2023 16:42:23 GMT

Hi,

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

Re: How to Sum and accurately summarize when conditions are in place

tamerj1 — Fri, 07 Jul 2023 17:10:43 GMT

Nico_89

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

Re: How to Sum and accurately summarize when conditions are in place

Nico_89 — Fri, 07 Jul 2023 17:30:37 GMT

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

Re: How to Sum and accurately summarize when conditions are in place

tamerj1 — Fri, 07 Jul 2023 17:38:02 GMT

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]
)
)
)

Re: How to Sum and accurately summarize when conditions are in place

Nico_89 — Fri, 07 Jul 2023 19:37:41 GMT

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! 😁