Measure that only includes values if all records in the same group are available

[mrrock]

Hi,

I have a data source that looks like this:

For which I unpivoted the Date columns in PBI. The rows are grouped by colour to try make it easier to see.

In my report, I'll have slicers for Entity, Account, and Date. The Date filter is single-select, and the others are multi-select. Like so:

This is my Model View:

I need to create a measure (SUM) that will only return figures if all relevant records for a particular GroupID are selected in the slicers.

For example:

If I select Date to 30/09/2020, then I can only see the figures for GroupID 3 if both "Entity 4" and "Entity 5" are selected in the slicer, and the Account slicer has "Borrowings", "Other Receivables", and "Retained Earnings" selected.

Similarly, for the same date, for GroupID 4, both "Entity 4" and "Entity 1" need to be selected, and also the Accounts "Other payables" and "Retained Earnings" selected.

If any relevant Entity or Account is not selected in the slicer, then the entire figure for the group will be zero.

Would anyone know how to achieve this? Thanks in advance.

[mrrock]

Hi @amitchandak ,

Thanks for the reply, and sorry it's not clear.

To give some context, the records in the [Transactions] table are consolidation entries in a balance sheet report for a company that has subsidiaries (the entities). Each GroupID represents a set of intra-company operations, for example one entity lending money to another entity within the company, and the total for each GroupID always sums to zero.

The requirement is that I only add figures for each group if none of the records are excluded by the entity and/or account filters. The figures will be reflected in the total for each account, but they will not affect the grand total of the balance sheet.

[amitchandak]

@mrrock , Not very clear. Try if one of the two can help

_Sum =
VAR _Entities =
CALCULATETABLE(VALUES('Table'[Entity]), ALLSELECTED('Table'[Entity]))

VAR _Accounts =
CALCULATETABLE(VALUES('Table'[Account]), ALLSELECTED('Table'[Account]))

RETURN
IF(
COUNTROWS(_Entities) = DISTINCTCOUNT('Table'[Entity]) &&
COUNTROWS(_Accounts) = DISTINCTCOUNT('Table'[Account]),
SUM('Table'[Value]),
0
)

or

_Sum= CALCULATE(
SUM('Table'[Amount]),
FILTER(ALLSELECTED('Table'), 'Table'[GroupID] = Max('Table'[GroupID]))
)