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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
StevenGrenier
Frequent Visitor

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.
 

Power Bi Help.jpg

 

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

 

Thank you!

1 ACCEPTED SOLUTION

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. 

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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