cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lucian
Helper V
Helper V

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

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:

 

pic0.jpg

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_NoEntry_TypePosting_DateDocument_TypeInitial_Entry_Due_DateAmount_LCY
433155Initial Entry30.07.2021Invoice29.08.2021165.84
433155Application30.08.2021Invoice29.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:

 

PIc1.jpg 

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.

 

 

pic2.jpg

 

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

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

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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

 

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:

pic1.jpg

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 

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

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.