Sorry about the long post, I need some help with a workaround I figured out for one of my reports but some background information is needed for clarity. Unfortunately I cannot include pictures or specific data as it is sensitive information. The data I have received (Ill call it Data 1) involves various performance indicators (ie. Revenue, Gross profit) which are to be displayed in a matrix which I will refer to as Table 1. The information is to be sliced with 2 slicers, one for Date (the Date column in Data 1 is connected to a Date table) and one for "Practice", which is also connected to a separate Practice table. The company is split up into separate Practices as each sells a different product, lets call them Practice A, Practice B and Practice C. However, I cannot sum up the Practices to get the "Overall Company" as the company total performance indicators are adjusted internally due to targets needing to be met, hence
Practice A + Practice B + Practice C is not equal to Overall Company.
The data I have recieved hence has 'Overall Company' as a separate practice, and therefore I do not compute totals for Table 1 and instead use the Practice slicer which contains: Pratice A, Practice B, Practice C and Overall Company. A single selection is required by the end user, otherwise the sum of Data 1 for the practices PLUS the Overall Company would be displayed.
Table 1 works well, however I have separate data (Ill call it Data 2) that I need to display in a separate table (Ill call it Table 2) but on the same report page. This separate data has to do with spending, and shows spend separated by "Spenders", but also needs to be sliced by Date and Practice with the SAME Practice slicer (one of the requirements of this report is to have only ONE Practice slicer on the top of the page) This separate data however does not have a separate 'Overall Company' Practice, and needs to be summed up to get to the company total. The Practice and Date columns in Data 2 are also connected to Date Table / Separate practice table (which feed into the slicers). Obviously, clicking on 'Overall Company' in the slicer results in an empty Table 2 as there is no practice in Data 2 called 'Overall Company', only Practice A, B and C. Hence I thought of the following workaround to calculate the spend in Table 2 which will find the total spend for Practice A + B + C when 'Overall Company' is selected in the slicer using SELECTEDVALUE:
Spend =
IF( SELECTEDVALUE(Table1[Practice])="Overall Company",
CALCULATE( SUM(Table2[Spend]),
ALLEXCEPT('Table2','Table2'[Spender],'Table2'[Date])
) ,
SUM('Table2'[Spend])
)Basically, when "Overall Company" is selected, the filter context is ignored, and all of the Spend data is displayed in the table for every Practice, except I still want to see the spend split by Spenders and I still want the Spend to change when I select a different Date in my Date slicer. The resulting table (once "Overall Company" is selected in the Practice slicer) indeed shows a summary of all practices, and the spend is split by spender, however - the Date slicer is ignored, and a sum of the spend for all dates is shown instead. If I include Date as a column in Table 2, I can see the split of spend by date but I want it to use the Date slicer, as there should be no Date column in the table. Adding 'Values('Table2'[Date]) to my CALCULATE expression just returns an empty Table 2, as I assume this brings back the filter context of 'Overall Company' not being a Practice in Data 2.
Any ideas on how I can fix this? Thank you very much for reading this far!
