cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

SUM of value based on filtered data and slicer multiple selection

I have a unique situation to filter the table based on condition and slicer and then sum of value if multiple selection in the filter.

 

I have a table1 with the shop name, projected sales value, and date. The date can be a past date or future date. The sample below in the image.

Table1  
   
ShopNameProjectedSalesDate
Shop110000001-Jan-20
shop120000004-Apr-20
Shop113000015-Oct-20
Shop224000003-Jun-20
Shop212500020-Nov-20

 

In the slicer, there are shop names coming from another dataset.

 

Report has KPI card that will display the value of selected shop for closest day to today in past. if multiple selection made in slicer, it should sum of value for selected shop.

For example, 

 

if shop1 selected - > card should have value of 200,000 as date is 4 Apr closest to today and it is in past.

if shop2 selected -> card should have value of 240000 as date is 3 june closest to today, other is in future.

if both shop selected - > card should have value of 440000, sum of value for shop1 and shop2.

 

I have tried so many things but couldn't get the desired output.

 

I have created an extra column for days different and created measure as below to get the value but it is only working for a single selection, if multiple shops selected, it is not working. Please help me with this.

 

ClosestdayValueByBU = CALCULATE(
VALUES('Monthly Sales Goals by BU'[ProjectedSales]),
FILTER('Monthly Sales Goals by BU' ,'Monthly Sales Goals by BU'[DaysDifferentToToday] = min('Monthly Sales Goals by BU'[DaysDifferentToToday]))
)
 
I have tried with SUM instead of Values, but not adding up value for multiple selection. Please help me on this.

 

 

Rajan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: SUM of value based on filtered data and slicer multiple selection

You definitely need to use SUM not VALUES. The VALUES function will only return a scalar result when you have a single row in the filter context.

 

Given your sample data something like the following should work:

 

ClosestdayValueByBU = CALCULATE(
SUM('Monthly Sales Goals by BU'[ProjectedSales]),
    FILTER('Monthly Sales Goals by BU' ,
        'Monthly Sales Goals by BU'[Date] = CALCULATE(max('Monthly Sales Goals by BU'[Date]), 
                ALLEXCEPT('Monthly Sales Goals by BU','Monthly Sales Goals by BU'[ShopName]), 
                'Monthly Sales Goals by BU'[Date] <= NOW() 
            )
    )
)

 

View solution in original post

1 REPLY 1
Highlighted
Super User II
Super User II

Re: SUM of value based on filtered data and slicer multiple selection

You definitely need to use SUM not VALUES. The VALUES function will only return a scalar result when you have a single row in the filter context.

 

Given your sample data something like the following should work:

 

ClosestdayValueByBU = CALCULATE(
SUM('Monthly Sales Goals by BU'[ProjectedSales]),
    FILTER('Monthly Sales Goals by BU' ,
        'Monthly Sales Goals by BU'[Date] = CALCULATE(max('Monthly Sales Goals by BU'[Date]), 
                ALLEXCEPT('Monthly Sales Goals by BU','Monthly Sales Goals by BU'[ShopName]), 
                'Monthly Sales Goals by BU'[Date] <= NOW() 
            )
    )
)

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors