Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Measures calculated based on 2 values depending on 2 indepent slicers

Happy New year

So my subject sounds confusing - please bear with me explaining this.

 

Lets say my scenario is that I would like to compare number of prints over a period of time.

However, I need to give users flexiblity to adjust time lines for example

 

Slicer 1: between year 2011-2013

Slicer 2: between year 2015-2016

 

I do this by putting 2 separate line charts, first one interaction depends on the 1st slicer and 2nd line chart depends on the 2nd slicer etc...

Say the charts represent the number of photo copies made during that period.

 

Now I need to do a comparison between the difference betweens the average of print daily in the selected time lines.

 

How can I achieve this? See, my end users may want to compare data on weekly basis or monthly basis.

 

Hope you can advice

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    guavaq's soution seems well, you can also take a look at below formulas which used to calculate the average value.

     

    Weekly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,WEEK)

Monthly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,MONTH)

AVG of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
AVERAGEX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])

     

    Notice, table is the source data of slicer.

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi Anonymous,

     

    What I would do to achieve this is to create a Calculated table which is a copy of your existing table. You can use the following by clicking on Modeling and then New Table

     

    Then the syntax would be:

    Table = TableToCopy

     

    Then what you could do is to then create a calculated measure on your "Table" which would be the difference between "TableToCopy" and "Table"

     

    Finally your one visual with it's slicer would be from "TableToCopy" and your other visual and slicer would be from "Table"

     

    And then your calculated measure "Difference" would be showing the difference between what was selected?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    guavaq's soution seems well, you can also take a look at below formulas which used to calculate the average value.

     

    Weekly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,WEEK)

Monthly AVG Of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
SUMX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])/DATEDIFF(datemin,datemax,MONTH)

AVG of Selected Range = 
var datemax= LASTDATE(ALLSELECTED(Table[Date]))
var datemin= FIRSTDATE(ALLSELECTED(Table[Date]))
return
AVERAGEX(FILTER(ALL(Table),[Date]>=datemin&&[Date]<=datemax),[Amount])

     

    Notice, table is the source data of slicer.

     

    Regards,

    Xiaoxin Sheng