cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Giadev
Frequent Visitor

Add hidden fixed value to a filter

Hi,

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

 

This s my table:

AccountValue
A10
B22
C23
D44
Z45
Z56
Z78



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

 

Power BIPower BI

 

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

 

Thanks

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

H@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 soYou 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 TABLE2SUM ( 'Table'[Value] ) + sumz"" )
RETURN
    last

 

final you will get the below:

v-luwang-msft_0-1614332635928.png

 

v-luwang-msft_1-1614332635930.png

 

 

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

H@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 soYou 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 TABLE2SUM ( 'Table'[Value] ) + sumz"" )
RETURN
    last

 

final you will get the below:

v-luwang-msft_0-1614332635928.png

 

v-luwang-msft_1-1614332635930.png

 

 

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

View solution in original post

CNENFRNL
Super User III
Super User III

@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] )
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.