Forum Discussion

Steve_Scotland's avatar
2 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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.

  • 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.