Forum Discussion

Frequent Visitor

5 years ago

Solved

Add hidden fixed value to a filter

Hi,

I need to add some hidden fixed values to a filtered selection.

This s my table:

Account	Value
A	10
B	22
C	23
D	44
Z	45
Z	56
Z	78

Slicer with hidden Account eq to "Z", but I need to sum all Accounts Z values plus values of other selected accounts.

Power BI

I hope it is harder to explain than to do it...

Thanks

v-luwang-msft
5 years ago
Hi Giadev ,
Is this what you want to get, when you select A at the slicer, you get the data of A plus the data sum of Z. If you select B at the slicer, you get the data of B plus the data sum of Z.If so，You could try the following dax to create a measure:
measure =
VAR sumz =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Account] = "Z" )
    )
VAR TABLE2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Account] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = SELECTEDVALUE ( 'Table'[Account] )
        )
    )
VAR last =
    IF ( MAX ( 'Table'[Account] ) IN TABLE2, SUM ( 'Table'[Value] ) + sumz, "" )
RETURN
    last

final you will get the below:

Wish it is helpful for you!

Best Regard
Lucien Wang

3 Replies

amitchandak
Super User
5 years ago
Giadev , better you create an independent Account table without values of Z

summarize(filter(Table, Table[Account] <>"Z"),Table[Account])

now you can create a new measure

Measure =

var _sel = allselected([Account])

return

calculate(sum(Table[Value]), filter(Table, Table[Account] = "Z" || Table[Account] in _sel))
CNENFRNL
Community Champion
5 years ago
Giadev , as you mentioned, you can add a fixed sum up of Z to the selected account this way,

Hidden Z = CALCULATE ( SUM ( Table1[Value] ), Table1[Account] = "Z" ) + SUM ( Table1[Value] )
v-luwang-msft
Community Support
5 years ago
Hi Giadev ,
Is this what you want to get, when you select A at the slicer, you get the data of A plus the data sum of Z. If you select B at the slicer, you get the data of B plus the data sum of Z.If so，You could try the following dax to create a measure:
measure =
VAR sumz =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Account] = "Z" )
    )
VAR TABLE2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Account] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Account] = SELECTEDVALUE ( 'Table'[Account] )
        )
    )
VAR last =
    IF ( MAX ( 'Table'[Account] ) IN TABLE2, SUM ( 'Table'[Value] ) + sumz, "" )
RETURN
    last

final you will get the below:

Wish it is helpful for you!

Best Regard
Lucien Wang