Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Semi-Additive Measures or how to ignore only one s...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

10-19-2021
01:34 PM

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

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

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2021
03:31 PM

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2021
02:10 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2021
03:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-20-2021
03:31 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2021
01:28 PM

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

281 | |

146 | |

63 | |

56 | |

40 |

Top Kudoed Authors

User | Count |
---|---|

339 | |

216 | |

86 | |

69 | |

57 |