cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Help with multiple value slicer selection

I have a fairly simple requirement which I can't find a solution to:

I want to see the sum of document value, over a large list of transactions, split by project code. They want 4 columns:
a) Project Code
b) Sum of Doc Value for a chosen period (or multiple periods - this is important)
c) Sum of Doc Value for another chosen period (or multiple)
d) The movement/delta between b) and c) (c minus b)

I've tried all sorts of solutions, the closest I got was curbal's "VALUES IN var" method shown at the end of this video - but it just isn't working for some reason: https://www.youtube.com/watch?v=_k_Qxb6pyCc
It seems to be calculating doc value across all time periods, regardless of what period/s I choose

 

I've got 2 disconnected 'Period' tables/slicers - one for column b) and c) and have tried the following measure (for column b):

Period 1 Value = 
VAR p1_selection =
    ALLSELECTED ( 'Period 1'[StartDate] )
RETURN
    CALCULATE (
        [Doc Value],
        FILTER (
            'dwh TransactionLineItem',
            'dwh TransactionLineItem'[Period Start Date] IN p1_selection
        )
    )

The idea behind this was that the user would select a period/s for column B in slicer B, and do the same for column C and slicer C, and the table would show project codes that had transactions in EITHER of the periods. I've got it working for if the user can only select one-period using a SELECTEDVALUE() solution, but now need to extend it out to multiple slicer selections, which led me to Curbal's video.
 

Can anyone point me in the right direction? I've posted a simple dataset showing how the data is structured

 

test data 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Help with multiple value slicer selection

Hi @omillzy ,

 

We can create two transaction period table and create two slicers using them to meet your requirement.

 

1. Create two period tables that just contain transaction period column.

 

Table 2 = DISTINCT('Table'[transaction period])

Table 3 = DISTINCT('Table'[transaction period])

 

Help1.jpg

 

2. Then we can create two measures, one calculates the sum of document value based on slicer 1, another bases on slicer 2.

 

Measure =
var selected_value = ALLSELECTED('Table 2'[period 1])
return
CALCULATE(SUM('Table'[document value (£)]),FILTER('Table','Table'[transaction period] in selected_value))

 

Measure 2 = 
var selected_value = ALLSELECTED('Table 3'[period 2])
return
CALCULATE(SUM('Table'[document value (£)]),FILTER('Table','Table'[transaction period] in selected_value))

 

Help2.jpg

 

3. At last we can create a measure to calculate the difference between [Measure 1] and [Measure 2].

 

difference = [Measure 2] - [Measure]

 

Help3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Help with multiple value slicer selection

Hi @omillzy ,

 

We can create two transaction period table and create two slicers using them to meet your requirement.

 

1. Create two period tables that just contain transaction period column.

 

Table 2 = DISTINCT('Table'[transaction period])

Table 3 = DISTINCT('Table'[transaction period])

 

Help1.jpg

 

2. Then we can create two measures, one calculates the sum of document value based on slicer 1, another bases on slicer 2.

 

Measure =
var selected_value = ALLSELECTED('Table 2'[period 1])
return
CALCULATE(SUM('Table'[document value (£)]),FILTER('Table','Table'[transaction period] in selected_value))

 

Measure 2 = 
var selected_value = ALLSELECTED('Table 3'[period 2])
return
CALCULATE(SUM('Table'[document value (£)]),FILTER('Table','Table'[transaction period] in selected_value))

 

Help2.jpg

 

3. At last we can create a measure to calculate the difference between [Measure 1] and [Measure 2].

 

difference = [Measure 2] - [Measure]

 

Help3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors