Substitute value when filtered

tnwcloud — Mon, 17 Jul 2023 06:19:45 GMT

I have a dataset where some Items are members of a Group. For Groups, one Item is designated Type of Parent and the others, if any, are designated Type of Child. Each Item not in a Group carries an Amount. For Groups, only the Parent carries the Amount, however there is a Display Amount field that shows the Amount against each Child Item members of Groups. A correct Total of Amount would be SUM(Amount), while a SUM(Display Amount) would overcount.

I need a measure that sums Amount but works even if the Parent item is filtered out via a slicer or a user selection. Effectively I need to Sum the Amount column, but if a specific Child is selected I need to subsitute the Display Amount. If a selection filters out the Parent but includes more than one Child I need to use the distinct Amount for the Group.

I've tried playing around with VALUES and DISTINCT but I haven't found a solution that works for both Group and non-Group items.

Item	Group	Type	Amount	Display Amount
1	A	Parent	3	3
2	A	Child		3
3	A	Child		3
4	B	Parent	5	5
5	B	Child		5
6	B	Child		5
7	C	Parent	3	3
8	C	Child		3
9	C	Child		3
10	null		7	7
11	null		5	5
12	null		2	2

Re: Substitute value when filtered

bot_damo — Mon, 17 Jul 2023 06:33:13 GMT

Hi tnwcloud,

I think the following DAX will work here.

CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table', 'Table'[Group]))

Kind Regards

Re: Substitute value when filtered

tnwcloud — Mon, 17 Jul 2023 13:49:23 GMT

Wow I was really over thinking that one!

topic Substitute value when filtered in DAX Commands and Tips

Substitute value when filtered

Re: Substitute value when filtered

Re: Substitute value when filtered