How to display the sum in totals row

[mp390988]

Hi,

I have the following which shows the formula behind the column called "CheckForBreach" but what I want in the totals for that column is the sum. Right now, cos I have applied ISINSCOPE, it has left the value in the totals for that column as blank but I want to diaplay the sum. How do I do this please?

Thank You

[v-venuppu]

Hi @mp390988 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @mp390988 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @mp390988 ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu]

Hi @mp390988 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Shahid12523 @MasonMA @collinq for the prompt response.

When you use SUMX( VALUES(Clients[ClientID]), … ), each row creates row context.

MAX(FactCombined[YearlyLimit]) by itself does not automatically pick up that row context.

Wrapping it in CALCULATE(MAX(FactCombined[YearlyLimit])) forces the row context to become a filter context - so MAX is evaluated per client correctly.

MAX respects filter context, but inside an iterator you need CALCULATE to turn the current row into a filter context. That’s why your totals work only with CALCULATE.

[Shahid12523 · Accepted solution]

To show the sum in the total row for CheckForBreach, modify your DAX like this:

CheckForBreach =
IF(
ISINSCOPE(Clients[ClientID]),
IF([mTTDVolume] > MAX(FactCombined[YearlyLimit]), 1, 0),
SUMX(
VALUES(Clients[ClientID]),
IF([mTTDVolume] > CALCULATE(MAX(FactCombined[YearlyLimit])), 1, 0)
)
)

✅ Rows show 1 or 0 per client.
✅ Total row shows the sum of breaches.

[mp390988]

Hi @Shahid12523 ,

thank you, your solution works.

for my understanding, can you please explain the effect of calculate in the following. I always thought the max function always respects the initial filter context.

CALCULATE(MAX(FactCombined[YearlyLimit]))

[mp390988]

it returns 1 in the total row which is not correct:

[mp390988]

it is a whole number but still doesn't give total

[MasonMA]

Hello @mp390988 

Instead of ISINSCOPE, you can make the measure behave differently at the total level by using an iterator over Clients in the total context. 

SUMX (
    VALUES ( Clients[ClientID] ),
    IF (
        [mYTDVolume] > MAX ( FactCombined[YearlyLimit] ),
        1,
        0
    )
)

[collinq]

Hi @mp390988 ,

For the totals to appear, the field needs to be a numeric field of some sort.  It is probably a text or boolean field at the moment.  All you should need to do is to make it a numeric field and the totals should appear!