cancel
Showing results for
Did you mean:
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 ShopName ProjectedSales Date Shop1 100000 01-Jan-20 shop1 200000 04-Apr-20 Shop1 130000 15-Oct-20 Shop2 240000 03-Jun-20 Shop2 125000 20-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]))
)

Rajan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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()
)
)
)``````

Highlighted
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()
)
)
)``````

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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