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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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