User Profile
Stewart_DM
Frequent Visitor
Joined 5 years ago
User Widgets
Contributions
Re: Remove filter in measure when viewing as a role
I managed to solve this. Easiest way I found was to duplicate my 'ACTUALS_DATA' table and remove the columns of the fields I did not want to filter by. So for example, i created 'Global_NR' which only included rows relating to net revenue, did not include Area or Region fields. I then concatenated the product and the period both in this new table and ACTUALS_DATA and created a relationship. Now, when ACTUALS_DATA is filtered in my report my measure still picks up the global net revenue from 'Global_NR'. If I filter on a specific product both ACTUALS_DATA and Global_NR are filtered so that the filtered region's % share of NR is calculated for that product. If all regions are selected in the report, the calculation will of course be 100%. Thanks Icey Your solution may well have worked but I didn't attempt it in the end as the above solution was successful.Re: Remove filter in measure when viewing as a role
Just to clarify. What I suspect is happening here is that the role filter is filtering the underlying data, meaning that my measure using ALL only sees the Africa data. What I need however is a way to create a role which filters all visuals to 'Africa' but does not ommit other areas from the data - so that a share of global revenue can be calculated.Remove filter in measure when viewing as a role
Hi, I have a report setup with some fairly complicated measures. One of the measures calculates the share of total net revenue by Area. For example, when I set the Area slicer to Africa the measure calculates that Africa is responsible for say 17% of total revenue. To do this i used: Net Revenue (act_adjusted) total for Area = CALCULATE([Net Revenue (excl IFRS)], all('ACTUALS_DATA'[Area]), all('ACTUALS_DATA'[Region])) This returns the total global revenue, I then divide the Africa revenue figure to get a share of total. Net Revenue (act_adjusted) divided by Net Revenue (act_adjusted) total for Area = round(DIVIDE( [Net Revenue (excl IFRS)], [Net Revenue (act_adjusted) total for Area] ),1) Net Revenue (excl IFRS) is filtered using the Area slicer (in this case Africa) and the total for Area is the global figure (as it uses the ALL statement). I now have a percentage of total attributable to Africa. All of this works great when I view the report with full accesss. However, a problem occurs when I view as role=Africa. The role is restircted to viewing only Area=Africa: As I am applying the filter here the exact same way I am via the slicer when viewing unrestricted I would expect the measure to behave in the same way but it appears that when viewing in the Africa role the initial measure is not removing the Area filter with the 'ALL' statement, instead it is still limited to Africa. Does anyone know a way of removing the Area filter just for this measure? I have tried using REMOVEFILTER instead of ALL but the same problem occured. Any help would be much appreciated! Thanks Stewart
