Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Steve_Scotland
Frequent Visitor

How to add up multiple selections from a slicer

I am probably showing my ignorance in this question expecting it to work when it doesn't :

 

I have a measure to lift Line Value from a SQL table Invoices


Sales Value = sum(Invoices[Line_Value])


I have Var in each further measure for

 

Reporting Week_TY / Reporting_Year_TY / Reporting Week_LY / Reporting_Year_LY

(where TY and LY are this year and last year respectively)

 

which has been suggested to me I change to measures too but that is a separate issue.


This then allows me to do further measures such as Example 1 to 2 where the only difference is whether I am calculating weeks weeks  this year or Last year in the 'Calc' bit. I'd like to to maintain my code integrity as I have other measures such as one for Profit that says - inevitably -

 

Profit = sum(Invoices[Profit])

 

so all I have to do is change my respective codes from [Sales Value] to [Profit]

 

Everything works swimmingly to calculate

 

Wholesale Sales Week This Year / Wholesale Sales Week Last Year

 

with a slicer for the year and week number which of course feeds in to

 

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

 

the problem is that the users now want to choose multiple weeks on the slicer so that if they choose weeks 15 / 16 / 17, say, the this year and last year are the sum of the chosen weeks for this year and last year

 

When I do it, this year is zero and last year is a sum which is not the combination


****************************

//  Example 1

 

Wholesale Sales Week This Year =

 

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])

VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1

Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1


// The Calc bit :

 

VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_TY && Dates[Year Int] = Reporting_Year_TY),

 

[Sales Value])

 

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)

 

*************************************

 

// Example 2

 

Wholesale Sales Week Last Year =

 

VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])

VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])

 

VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1

Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1

 

// The Calc bit :

 

VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_LY && Dates[Year Int] = Reporting_Year_LY),

 

[Sales Value])

 

Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)


****************

2 REPLIES 2
Steve_Scotland
Frequent Visitor

@Greg_Deckler 

 

Thank you.

 

Unfortunately that went right over my head at the moment 😞

 

I was hoping to change my code slightly to maintain the integrity and re-usability with all my date VAR and subsequent calcs where in the 'Calc bit' above   "all" I had to do was change the date range and what I was scanning for   (Sales / Profit / Budget etc)

 

Took me an absolute age to get to where I am now.  They said they wanted Week / Period / YTD for Sales / Profit versus last year and versus Budget  and now they don't.  Or rather it is "Yes, but........."

 

PS:  I have your book 🙂

I've been written to by someone famous!

Last time that happened to me was when Miriam Margolyes wrote to me on CompuServe, he says showing his age.

Greg_Deckler
Community Champion
Community Champion

@Steve_Scotland You can't use SELECTEDVALUE when multiple values are selected. You will want to use SELECTCOLUMNS to to return your values as a single column table. You could also use DISTINCT (column form) or VALUE (not recommended). You can then use the IN operator against this table var to filter on.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors