Forum Discussion

StevenGrenier's avatar
StevenGrenier
Frequent Visitor
7 years ago
Solved

Multiple Slicers for Same Column +Calculations Based on Selections

Hi,

 

I'm building a pretty heavy report for a client which displays survey results. This client has very particular needs : she wants to be able to see on the same screen :

  • the results of three individual store branches (selected month vs previous month of selection)
  • the entire network's results (selected month vs previous month of selection)
  • a 6 month comparison of the 3 branches vs the network.

 

That's easy to do with page filters when the three branches are set in stone. What she wants is to be able to selected which 3 branches (out of their 30 branches) to display. So far that's also easy using "Edit Interactions": am able to assign one slicer per set of columns. But when comes the time to calculate the 6 month average of those selected branches and calculate the N count (bottom left corner - should read 17 since in July for those three branches, there was a total of 17). I don't know how to calculate this properly.

 

An example of a measure that calculates the 6 month average: 

Global_Average_L6M = (CALCULATE([Global_Average];DATEADD('Calendar'[Date];-1;MONTH)) + CALCULATE([Global_Average];DATEADD('Calendar'[Date];-2;MONTH)) + CALCULATE([Global_Average];DATEADD('Calendar'[Date];-3;MONTH)) + CALCULATE([Global_Average];DATEADD('Calendar'[Date];-4;MONTH)) + CALCULATE([Global_Average];DATEADD('Calendar'[Date];-5;MONTH)) + CALCULATE([Global_Average];DATEADD('Calendar'[Date];-6;MONTH))) / 6
 
My dataset is set up so that each questions is a column, and each row represent respondents' answers to those questions. Other columns include the branch names, dates and so on.
 
I have a calendar table for dates controls and formats.
 
Each branch slicer only filters the data in the columns beneath it. The date slicers filter the entire page.
 

 

If someone can help me figure out how to achieve the client's desires, I would very much appreciate it. 

 

Thank you!

Need Help
  • StevenGrenier's avatar
    StevenGrenier
    7 years ago

    Unfortunately, due to confidentiality and the fact that the dataset is in direct query, I was unable to share more information. Nonetheless, I was able to devise a solution, by creating three tables with the same branch information each linked to copies of the same column in the main dataset. With this, I was able to use dax and achieve the result I wanted. 

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi StevenGrenier ,

     

    Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    • StevenGrenier's avatar
      StevenGrenier
      Frequent Visitor
      7 years ago

      Unfortunately, due to confidentiality and the fact that the dataset is in direct query, I was unable to share more information. Nonetheless, I was able to devise a solution, by creating three tables with the same branch information each linked to copies of the same column in the main dataset. With this, I was able to use dax and achieve the result I wanted.