Semi-Additive Measures or how to ignore only one slicer selection

[Lucian]

Hello,

I have a problem creating a measure that would calculate properly (including total row) and make it ignore a single slicer selection (but keeping the others).

The "factCustomers" table definition is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZU/b8IwEMW/CmJqJQedfed/3Tp06NTuiAHRDJFoQChF4ts3cew2KBejliyRowf+6Z7fi9frJRkw3i7F8rWummq7X7zUzenSvitYgV0pUHIB8AQQfnI+VLuyXQGtwF2LyoNQrluY9vF2Lk8fX+XiQRYIj7/yRnRIZZzhkJhD2hHSoJbCy/BPjpn0CLUwB9SiFuQnmVEOSESpNYPEnLXKj5DSaOGoW3DEqCaik3MQveyH5Im9Gok2nPq9RHJKCZocMsmBSdJb+itTjiNrSAkTHGTTE9Q+O+RRzQCU4FBoN4lMeu8sacOlB3C07/v28lnWDS8WbduECi2xHDTp6TglFyCm8INJx8dpncBuH8meZq9Ga5XkagnjPQf5YaxFdMJ2fvHORjkxcQ6mRRJkppBRTQHSXIDk+LgGRDcSEcmKkH3FIZOcmA5nYBKJ8LVmib0YWwmTF8lkXhmx+MkOsHEdhCdV5Pl43Fe7bVMd6n8U5EY/uHpcA5khsuUo8u0YTphMvQnMWpp3dGhovLGucZibj/mWF/kLq+BurHuJ2QsryZvNNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entry_No = _t, Entry_Type = _t, Posting_Date = _t, Document_Type = _t, Initial_Entry_Due_Date = _t, Amount_LCY = _t, #"Customer PastDue" = _t, #"Age Group" = _t, #"Customer Sold per Aging Grup" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry_No", Int64.Type}, {"Entry_Type", type text}, {"Posting_Date", type datetime}, {"Document_Type", type text}, {"Initial_Entry_Due_Date", type datetime}, {"Amount_LCY", type number}, {"Customer PastDue", Int64.Type}, {"Age Group", type text}, {"Customer Sold per Aging Grup", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

And will look like this:

This table contains the invoices and/or payments with Entry_Type = "Initial Entry" and the Navision application will automatically add the corresponting records with the same Entry_No but the Entry_Type = "Application" when the values are "deducted"/paid.

Based on Initial_Entry_Due_Date was calculated at selected date (30.08.2021) which invoices are PastDue to see how late are the payments - in the table above the Customer PastDue was used as "values" but in real report is in fact a measure (but I don't think this is influencing my following measures).

Now I'm required to calculate some measures.

First one, very simple:

mx Original Amount = 
CALCULATE (
    SUM ( factCustomers[Amount_LCY] ),
    KEEPFILTERS ( factCustomers[Entry_Type] = "Initial Entry" )
)

This measure should show the values for Amount_LCY only where [Entry_Type] = "Initial Entry".

The second one should calculate the remaining amount, summing the Amount_LCY, for the same Entry_No, ignoring the Entry_Type:

mx Remaining Amount = 
VAR vEntryNo =
    MAX ( factCustomers[Entry_No] )
VAR vEntryType =
    MAX ( factCustomers[Entry_Type] )
VAR vResult =
    IF (
        vEntryType <> "Initial Entry",
        BLANK (),
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            FILTER (
                ALL ( factCustomers ),
                factCustomers[Entry_No] = vEntryNo
                    && factCustomers[Posting_Date] <= MAX ( dimDate[Date] )
                    && factCustomers[Initial_Entry_Due_Date] <= MAX ( dimDate[Date] )
            )
        )
    )
RETURN
    ROUND ( vResult, 2 )

More exactly for this pair of records:

Entry_No	Entry_Type	Posting_Date	Document_Type	Initial_Entry_Due_Date	Amount_LCY
433155	Initial Entry	30.07.2021	Invoice	29.08.2021	165.84
433155	Application	30.08.2021	Invoice	29.08.2021	-165.84

the Remaining Amount should be 0, but the value should be shown only on the rows with [Entry_Type] = "Initial Entry".

The problem with this measure is the wrong total (it shows only the last non blank or zero value) instead of adding all of the displayed values for rows:

 

To correct the total value I have tried with a second measure:

mx Remaining Amount2 = 
VAR vEntryNo =
    MAX ( factCustomers[Entry_No] )
VAR vEntryType =
    MAX ( factCustomers[Entry_Type] )
VAR vResult =
    ROUND (
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            FILTER (
                ALL ( factCustomers ),
                factCustomers[Entry_No] = vEntryNo
                    && factCustomers[Posting_Date] <= MAX ( dimDate[Date] )
                    && factCustomers[Initial_Entry_Due_Date] <= MAX ( dimDate[Date] )
            )
        ),
        2
    )
VAR vTotalResult =
    ROUND (
        CALCULATE ( SUM ( factCustomers[Amount_LCY] ), ALLSELECTED ( factCustomers ) ),
        2
    )
RETURN
    IF (
        HASONEVALUE ( factCustomers[Entry_No] ),
        IF ( vEntryType = "Initial Entry", vResult, BLANK () ),
        vTotalResult
    )

This is almost what I need, except the fact that when selecting in the slicer only "Initial Entry" option to get rid of the "Application" type records, I would like to show the total of 19.392,11 as in the initial picture, not 19.481,23 as in the picture below.

So, is it possible to make either of the two versions for "Remaining Amount" to calculate total for the displayed values? - because the rows will still show proper values - the total is my problem.

For convenience, the complete report is available (for 30 days, because of the sharing policy) as PBIX here: MeasureTotals.pbix

Kind Regards,

Lucian

[AlexisOlson]

You can use ALLEXCEPT to remove any filters except Entry_No.

How about something much simpler like this?

mx Remaining Amount =
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No] )
    )
)

[Lucian]

Hi @AlexisOlson 

First I would like to thank you for your time spent on my problem, and I'm amazed on how "lite" your formula is it. 😲

At first sight I was thrilled because it seemed to work perfectly, but then I have realized that I forgot to mention that the "Remaining Value" should be calculated depending on the "Selected Date".

So I have to apologize for this "missing little detail", that would cause a problem when selecting another date like August 27th:

In this case my formulas (either one) will not display the "Remaining Amount" on the rows that are "not due" at the selected date (based on Initial_Entry_Due_Date). Because of my missinformation, your formula named "mx Remaining Amount3" in the picture above will return wrong results on rows and of course the total will also be wrong. At the new selected date the total should be 11.177,07.

Could you please tell me how I could solve the total in this case?

Kind Regards,

Lucian 

[AlexisOlson · Accepted solution]

Yeah, I wasn't quite sure exactly how the date was supposed to come into play, so I figured I'd start simple and then add back complexity as needed.

If you have an active relationship so dimDate[Date] filters factCustomers[Initial_Entry_Due_Date], then you just need to add dimDate[Date] to ALLSELECTED.

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No], dimDate[Date] )
    )
)

If you don't want active relationships, then you have to do a bit more work. Either activate the relationship in the measure

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No], factCustomers[Initial_Entry_Due_Date] ),
        USERELATIONSHIP( factCustomers[Initial_Entry_Due_Date], dimDate[Date] )
    )
)

Or else apply the filter a bit more manually like this

mx Remaining Amount = 
IF (
    "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
    CALCULATE (
        SUM ( factCustomers[Amount_LCY] ),
        ALLEXCEPT ( factCustomers, factCustomers[Entry_No] ),
        factCustomers[Initial_Entry_Due_Date] IN VALUES ( dimDate[Date] )
    )
)

 or this

mx Remaining Amount =
VAR MaxDate = MAX ( dimDate[Date] )
RETURN
    IF (
        "Initial Entry" IN VALUES ( factCustomers[Entry_Type] ),
        CALCULATE (
            SUM ( factCustomers[Amount_LCY] ),
            ALLEXCEPT ( factCustomers, factCustomers[Entry_No] ),
            factCustomers[Initial_Entry_Due_Date] <= MaxDate
        )
    )

[Lucian]

Hi @AlexisOlson ,

Thank you sooo much!!! 🎉

That's it! All of your last three formulas work perfect, as I don't have any active relationship with the dimDate table.

So thank you again for your time! 

Kind Regards,

Lucian